Intro about AI

 

Getting data from different tables using measure

 To create a measure in Power BI that retrieves the confirmed_date from table2 based on the ticket_num in table1, you can use DAX (Data Analysis Expressions). Here is a step-by-step guide:

1. Open Power BI Desktop and load your data tables (table1 and table2).

2. Create Relationships:

   • Go to the "Model" view.
   • Create a relationship between table1 and table2 using the ticket_num column.

3. Create a New Measure:

   • Go to the "Report" view.
   • Click on the table1 in the Fields pane.
   • Click on "New Measure" in the ribbon.

4. Write the DAX Formula:

   • In the formula bar, enter the following DAX formula to create the measure:

Confirmed Date Measure = 
CALCULATE(
    MAX(table2[confirmed_date]),
    FILTER(
        table2,
        table2[ticket_num] = SELECTEDVALUE(table1[ticket_num])
    )
)

This measure does the following:

• CALCULATE is used to change the context in which the data is evaluated.
• MAX(table2[confirmed_date]) retrieves the maximum confirmed date from table2. Since table2 has a one-to-many relationship with table1, this will effectively retrieve the confirmed date for the corresponding ticket_num.
• FILTER(table2, table2[ticket_num] = SELECTEDVALUE(table1[ticket_num])) filters table2 to match the ticket_num from the currently selected row in table1.

5. Use the Measure:
   • You can now use the Confirmed Date Measure in your reports, tables, or visualizations to display the confirmed date from table2 for each ticket_num in table1.

This approach ensures that the confirmed date from table2 is correctly associated with the corresponding records in table1 based on the ticket_num column.

Create Power BI visuals with Python

Create Power BI visuals using Python in Power BI Desktop - Power BI | Microsoft Learn

AI Websites

Bolt-- https://bolt.new/ -For coding

Cursor--https://cursor.com/ -For coding

For Building Dashboards--https://www.thebricks.com/

Check New AI Tools launch each day -- https://openrouter.ai/

Analyze data in Excel -- https://numerous.ai/

Python Packages for Statistical Charts

SeaBorn-- https://seaborn.pydata.org/examples/index.html

Plotly --https://plotly.com/python/

D3-- https://d3js.org/

Multiply & divide with One column & one measure

• Below [RCTQTY] is a column & [Sampling Measure] is a measure

Expected Uploads Measure1 =

SUMX(

    Receipts_SAP,

    Receipts_SAP[RCTQTY] * [Sampling Measure]

)

• Below [SN_Count] is a column & [Expected Uploads Measure1] is a measure

Upload Percentage =

DIVIDE(SUM(Receipts_LSQD[SN_Count]), [Expected Uploads Measure1])

DAX Measure to calculate % of tickets closed within TAT

 %TICKETS_CLOSED_WITHIN_TAT(%Success) =

VAR FilteredTable =

    FILTER(

        'Closed,Rejected',

        'Closed,Rejected'[TAT_Closed_Req] = "Within TAT" //[TAT_Closed_Req] Is a measure

    )

RETURN

DIVIDE(

    CALCULATE(COUNT('Closed,Rejected'[Id]), FilteredTable),

    CALCULATE(COUNT('Closed,Rejected'[Id]))

)

Create Button Slicer for Monthly, Weekly & yearly Data

Video Ref:

https://www.youtube.com/watch?v=NA8KcFgxrG0

Step-1:

• Modelling  > New Parameter > Fields

Step-2:

• Select the parameters for Monthly,Weekly,Yearly as shown below

Step-3:

• After this, Insert button slicer and click on the parameter name which you created.

Ageing calculation using DAX

• In the Below DAX function we are calculating ageing only for the tickets which are open.
• If the Ticket is closed, then it is mentioned as "NA" 

DAX Code

Ageing Working Days =

var status1=SELECTEDVALUE(Main[Status])

RETURN

if(

    status1 in {"Open"},

CALCULATE(

    COUNTROWS('fct_Date Table'),

    DATESBETWEEN('fct_Date Table'[Date], max('Main'[SubmittedDate]), TODAY() - 1),

    'fct_Date Table'[IsWorkingDay] = TRUE,'Main'[Status] IN {"Open"},

    ALL('Main')),

    "NA"

)

Creating Date Table in Power Bi

Date_Table = ADDCOLUMNS(

CALENDAR(DATE(2023,6,1)

,TODAY()),

"Month-Year",FORMAT([Date],"mmm")&"-"&YEAR([Date]),

"Month",FORMAT([Date],"mmm"),

"MonthNum",FORMAT(MONTH([Date]),"00"),

"Week-Year","Week-" &WEEKNUM([Date],2)&"-"&YEAR([Date]),

"Year",YEAR([Date]),

"YearMonth", YEAR([Date]) & FORMAT(MONTH([Date]), "00"),

"IsWorkingDay",NOT WEEKDAY( [Date] ) IN { 1,7 }

)

Calculating Working Days in Power Bi DAX

References :

https://dax.guide/weekday/

 Step-1:

Create a calculated column in Date table.

"IsWorkingDay",NOT WEEKDAY( [Date] ) IN { 1,7 }




Step-2: Workingdays to Close

Working_Days_to_Close =

CALCULATE(

    COUNTROWS('fct_Date Table'),

    DATESBETWEEN('fct_Date Table'[Date], max('RefTable'[SubmittedDate]), max('RefTable'[ClosedDate]) - 1),

    'fct_Date Table'[IsWorkingDay] = TRUE,

    ALL('RefTable')

)

Step-3: Days to Close

Days to Close =

var submitdate=max('RefTable'[SubmittedDate])

var closeddate=max('RefTable'[ClosedDate])

RETURN CALCULATE(DATEDIFF(submitdate,closeddate,DAY),ALL('fct_Date Table'))

• ALL Function-> This function removes any filters that might be applied to the 'RefTable'. It ensures that the calculation considers all rows in the 'RefTable', regardless of any existing filters.

Power Bi Links

Sample Project Links

 https://community.fabric.microsoft.com/t5/Translytical-Task-Flow-Gallery/bd-p/pbi_translyticalgallery

Creating Progress Visual in real time

Beautiful SVG Targets vs Actuals Indicators in Power BI (No Custom Visuals!)

Download Animated Icons

4000+ Free High-quality Animated Icons

UI/UX Toolkit

Microsoft Power BI

DAX measure to get the Id record of last updated date

Problem:

I have a case where I need to display only the latest updated record for a Id.

In the below image I need to display only the record which is updated on 11-06-2025 (Latest)

Solution:

DAX Measure

IsLatestActivity = 
VAR CurrentIQMS = MAX('POR D'[Id])
VAR CurrentDate = MAX('POR D'[Updated Date])

VAR LatestRow =
    TOPN(
        1,
        FILTER(
            ALL('POR D'),
            'POR D'[Id] = CurrentIQMS
        ),
        'POR D'[Updated Date], DESC
    )
RETURN
IF (
    COUNTROWS(
        FILTER(
            LatestRow,
            'POR D'[Id] = CurrentIQMS &&
            'POR D'[Updated Date] = CurrentDate
        )
    ) > 0,
    1,
    0
)