Forum Discussion

manojk_pbi's avatar
manojk_pbi
Icon for Helper V rankHelper V
6 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 ex...
Need Help
Show and Tell
Tips and Tricks
  • v-aatheeque's avatar
    v-aatheeque
    6 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 :

     

     

v-aatheeque's avatar
v-aatheeque
Icon for Community Support rankCommunity Support
6 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 :