Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I am currently working on the validation project. I have the following 2 sample modules in the PowerBI dashboard. In Table 1, column S_Contrib_R is a custom column created using the formula below to look up using Unique Key and populate the data from Table 2.
S_Contrib_R = CALCULATE(SUM('Table 2'[CONTRIB_Master]), FILTER('Table 2', 'Table 2'[Unique Key] = Table 1[Unique Key]))
My requirement is Same column i.e S_Contrib_R needs to be modified such way using conditional statement that: -
Please help me with the above conditional statement where I can look up table 2 and whichever condition gets satisfied it will populate the data using Unique Key as identifier.
Solved! Go to Solution.
Hi @Bansi008 .
Pleas try this:
formula =
VAR S_Contrib_R =
CALCULATE (
SUM ( 'Table 2'[CONTRIB_Master] ),
FILTER ( 'Table 2', 'Table 2'[Unique Key] = EARLIER ( 'Table 1'[Unique Key] ) )
)
VAR CONTRIB_R_MTD =
CALCULATE (
SUM ( 'Table 2'[CONTRIB_R_MTD] ),
FILTER ( 'Table 2', 'Table 2'[Unique Key] = EARLIER ( 'Table 1'[Unique Key] ) )
)
RETURN
IF (
'Table 1'[Purchase] = "P",
CONTRIB_R_MTD,
COALESCE ( CONTRIB_R_MTD, S_Contrib_R )
)
If this is not what you're looking for, please post a workable sample data (not an image) and your expected result using the same sample data.
Proud to be a Super User!
Hi @Bansi008 .
Pleas try this:
formula =
VAR S_Contrib_R =
CALCULATE (
SUM ( 'Table 2'[CONTRIB_Master] ),
FILTER ( 'Table 2', 'Table 2'[Unique Key] = EARLIER ( 'Table 1'[Unique Key] ) )
)
VAR CONTRIB_R_MTD =
CALCULATE (
SUM ( 'Table 2'[CONTRIB_R_MTD] ),
FILTER ( 'Table 2', 'Table 2'[Unique Key] = EARLIER ( 'Table 1'[Unique Key] ) )
)
RETURN
IF (
'Table 1'[Purchase] = "P",
CONTRIB_R_MTD,
COALESCE ( CONTRIB_R_MTD, S_Contrib_R )
)
If this is not what you're looking for, please post a workable sample data (not an image) and your expected result using the same sample data.
Proud to be a Super User!
Thanks, this perfectly works as expected.
User | Count |
---|---|
86 | |
83 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |