Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi there,
I got a data which contains the registration number and authorisation type of the registration number. I want to calculate the no of registration number that are holding two types of authorisation types i.e. LAT and PROV or LAT and Tōmua/Prov. I created a measure as follow:
Solved! Go to Solution.
I'm not sure I'm interpreting your question correctly, but maybe this will be closer to what you're after:
Two types =
SUMX (
VALUES ( Authorisation[Individual__r.Registration_Number__c] ),
IF (
CALCULATE (
DISTINCTCOUNT ( Authorisation[Authorisation_Type__c] ),
Authorisation[Authorisation_Type__c] IN { "LAT", "PROV", "Tōmua/Prov" }
) = 2,
1,
0
)
)
Hi,
Since you have not described what you want, i am just making a guess of desired result. Try this measure
2 types try = SUMX(VALUES(Sheet1[Registration_Number__c]),
VAR _auth = CALCULATE(MAX(Sheet1[Authorisation_Type__c]))
RETURN IF(_auth="LAT" || (_auth = "PROV" || _auth = "Tōmua/Prov") ,1,0))
The result i get is 26,566
This is returning 0 because the single-value-variable _auth cannot logically equal "LAT" and any other value at the same time.
Also, looking at your data, each Registration_Number__c occurs only in a single row, so I don't see how it can be associated with more than one authorization type.
@AlexisOlson , Sorry I updated the data sample and also updated the measure but still getting the same result i.e. 0
I'm not sure I'm interpreting your question correctly, but maybe this will be closer to what you're after:
Two types =
SUMX (
VALUES ( Authorisation[Individual__r.Registration_Number__c] ),
IF (
CALCULATE (
DISTINCTCOUNT ( Authorisation[Authorisation_Type__c] ),
Authorisation[Authorisation_Type__c] IN { "LAT", "PROV", "Tōmua/Prov" }
) = 2,
1,
0
)
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |