Help to create DAX Measure

[manojk_pbi]

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:

PrjID	PrjName	Last Passed Gate	Gate Passed Dt	Ageing	Gates Range	LL Report
P1	Prj1	G5	1-Apr-25	0	G3-G5	Yes
P2	Prj2	G4	1-Apr-25	0	G3-G5	Yes
P3	Prj3	G5	1-Jan-26	46 days	G3-G5	No
P4	Prj4	G5	1-Apr-25	0	G3-G5	Yes
P5	Prj5	G0	1-Apr-25	0	G0-G2	Yes
P6	Prj6	G2	10-Jan-26	36 days	G0-G2	No

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 Table	LL %
G0-G2	2	50%
G3-G5	4	75%

Master Data

PrjID	PrjName	Last Passed Gate	Gate Passed Dt
P1	Prj1	G5	1-Apr-25
P2	Prj2	G4	1-Apr-25
P3	Prj3	G5	1-Jan-26
P4	Prj4	G5	1-Apr-25
P5	Prj5	G0	1-Apr-25
P6	Prj6	G2	10-Jan-26

TransactionTable
PrjID	Gate	Gate Date
P1	G0	2-Feb-24
P1	G2	2-Jun-24
P1	G5	2-Dec-24
P2	G2	2-Feb-24
P2	G4	2-Dec-24
P4	G2	2-Jun-24
P4	G5	2-Feb-06
P5	G2	1-Apr-26

[v-aatheeque · Accepted solution]

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 :

[manojk_pbi]

@v-aatheeque , thanks for your reply. Your solution and explanation helps

I have posted one more requirement, please guide me . 

https://community.fabric.microsoft.com/t5/Developer/Calculated-Columns-or-Measure-to-get-the-output/...