Forum Discussion

manojk_pbi's avatar
manojk_pbi
Helper V
5 months ago
Solved

Help to create DAX Measure

Hello Friends,

 

Please guide me on below requirements.

 

I have two tables below, I need to compare the data in master table with transaction table to check entries and report in KPI.

In the expected out. we have master table data along with few calculated columns or we can have dax measure to get the information.

We need to calculate Ageing based on the Gate Passed date w.r.t o today. This calculation s only for those projects where we don't have corresponding data in transaction table. eg: for Prj3 we don't G5 entry in Transaction table so it is due from 46 days.

Gate Range column  - if Gate is G0 to G2 will be grouped as G0-G2 and others G3-G5.

LL Report : If yes when corresponding entry present in transaction table

Expected Out put:

PrjIDPrjNameLast Passed GateGate Passed DtAgeingGates RangeLL Report 
P1Prj1G51-Apr-250G3-G5Yes
P2Prj2G41-Apr-250G3-G5Yes
P3Prj3G51-Jan-2646 daysG3-G5No
P4Prj4G51-Apr-250G3-G5Yes
P5Prj5G01-Apr-250G0-G2Yes
P6Prj6G210-Jan-2636 daysG0-G2No

 

If i need to show data like below based on below what would be my DAX

Output 2

Total Projects based on Master Table : No of projects in the gate G0 to G2 & G3-G5

LL% : out of total projects how many projects have corresponding entries in transaction table

         G0-G2; we have 2 projects but only one project has entry in transaction table. so it is 50%

 Total Projects based on Master TableLL %
G0-G2250%
G3-G5475%

Master Data

PrjIDPrjNameLast Passed GateGate Passed Dt
P1Prj1G51-Apr-25
P2Prj2G41-Apr-25
P3Prj3G51-Jan-26
P4Prj4G51-Apr-25
P5Prj5G01-Apr-25
P6Prj6G210-Jan-26

 

TransactionTable 
PrjIDGateGate Date
P1G02-Feb-24
P1G22-Jun-24
P1G52-Dec-24
P2G22-Feb-24
P2G42-Dec-24
P4G22-Jun-24
P4G52-Feb-06
P5G21-Apr-26
Need Help
Show and Tell
Tips and Tricks
  • v-aatheeque's avatar
    v-aatheeque
    5 months ago

    Hi manojk_pbi 
    Thanks for reaching out to Microsoft Fabric Community Forum. 

    Thanks for providing the sample data. I’ve reproduced the scenario end-to-end in Power BI and validated the expected output.

    you’re working on creating DAX measures and calculations to analyze the data between your master and transaction tables. Here’s a guide on how to approach your requirements:

     

    Load the Master and Transaction tables with the provided sample data.

    Create a 1:* relationship on PrjID (Master  to TransactionTable).

    Create a calculated column for Gate Range:Gate Range = SWITCH( TRUE(), Master[Last Passed Gate] IN {"G0","G1","G2"}, "G0-G2", Master[Last Passed Gate] IN {"G3","G4","G5"}, "G3-G5", "Other" )Ageing (Days)_t = VAR AsOfDate = DATE ( 2025, 11, 16 ) RETURN IF ( Master[Gate Passed Dt] <= AsOfDate, 0, DATEDIFF ( AsOfDate, Master[Gate Passed Dt], DAY ) ) test = VAR AsOfDate = SWITCH ( TRUE (), Master[Gate Range] = "G3-G5", DATE ( 2025, 11, 16 ), Master[Gate Range] = "G0-G2", DATE ( 2025, 12, 5 ) ) RETURN IF ( Master[Gate Passed Dt] <= AsOfDate, 0, DATEDIFF ( AsOfDate, Master[Gate Passed Dt], DAY ) )
    The Expected output as below :

     

     

2 Replies

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    5 months ago

    Hi manojk_pbi 
    Thanks for reaching out to Microsoft Fabric Community Forum. 

    Thanks for providing the sample data. I’ve reproduced the scenario end-to-end in Power BI and validated the expected output.

    you’re working on creating DAX measures and calculations to analyze the data between your master and transaction tables. Here’s a guide on how to approach your requirements:

     

    Load the Master and Transaction tables with the provided sample data.

    Create a 1:* relationship on PrjID (Master  to TransactionTable).

    Create a calculated column for Gate Range:Gate Range = SWITCH( TRUE(), Master[Last Passed Gate] IN {"G0","G1","G2"}, "G0-G2", Master[Last Passed Gate] IN {"G3","G4","G5"}, "G3-G5", "Other" )Ageing (Days)_t = VAR AsOfDate = DATE ( 2025, 11, 16 ) RETURN IF ( Master[Gate Passed Dt] <= AsOfDate, 0, DATEDIFF ( AsOfDate, Master[Gate Passed Dt], DAY ) ) test = VAR AsOfDate = SWITCH ( TRUE (), Master[Gate Range] = "G3-G5", DATE ( 2025, 11, 16 ), Master[Gate Range] = "G0-G2", DATE ( 2025, 12, 5 ) ) RETURN IF ( Master[Gate Passed Dt] <= AsOfDate, 0, DATEDIFF ( AsOfDate, Master[Gate Passed Dt], DAY ) )
    The Expected output as below :