Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, please help me find a way to make two measures with the following logic:
Firstly, a link is dictated by Table 2 (Link column) back to Table 1 (UnID column) as in the shown image.
Measure 1: Count the number of "STR" that have at least one link with an "EER" or "SYS" = 3 (
Explanation: 1 (ArtID 1 has link with an EER or SYS) + 0 (ArtID3 has no link with an EER or SYS) + 1 (ArtID 6 has one link with EER or SYS) + 1 (ArtID 8 has link with an EER or SYS)
Measure 2:
Number of "STR" with implementation in {"implemented"} OR ALL linked "EER" and "SYS" with implementation in {"implemented"} =2
Explanation: 1 (ArtID 1 is "implemented") + 0 ( ArtID 3 "str" not_implemented and all links are in not_implemented) + 0 (Art 6 "str" not_implemented and not ALL links are implemented, only O-12 is implemented) + 1 (Art 8 "str" not_implemented but all links are in implemented, O-15 and O-12)
Solved! Go to Solution.
Hi @uif19085 ,
You can create a measure as below to get it, please find the details in the attachment.
Measure2 =
VAR _t1count =
CALCULATE (
DISTINCTCOUNT ( 'Table 1'[ArtID] ),
FILTER (
'Table 1',
'Table 1'[STR] = 1
&& 'Table 1'[Implementation] = "implemented"
)
)
VAR _tab =
CALCULATETABLE (
VALUES ( 'Table 1'[ArtID] ),
FILTER ( 'Table 1', 'Table 1'[STR] = 1 )
)
VAR _tab1 =
CALCULATETABLE (
VALUES ( 'Table 1'[ArtID] ),
FILTER (
'Table 1',
'Table 1'[STR] = 1
&& 'Table 1'[Implementation] <> "implemented"
)
)
VAR _uids =
CALCULATETABLE (
VALUES ( 'Table 2'[Link] ),
FILTER ( 'Table 2', 'Table 2'[ArtId] IN _tab1 )
)
VAR _uids1 =
CALCULATETABLE (
VALUES ( 'Table 1'[UnID] ),
FILTER (
'Table 1',
'Table 1'[UnID]
IN _uids
&& 'Table 1'[Implementation] = "implemented"
&& ( 'Table 1'[SYS] = 1
|| 'Table 1'[EER] = 1 )
)
)
VAR _tab2 =
SUMMARIZE (
FILTER ( 'Table 2', 'Table 2'[ArtId] IN _tab1 ),
'Table 2'[ArtId],
"@count1", COUNT ( 'Table 2'[Link] ),
"@count2",
CALCULATE (
COUNT ( 'Table 2'[Link] ),
FILTER ( 'Table 2', 'Table 2'[Link] IN _uids1 )
)
)
VAR _linkcount =
COUNTX ( FILTER ( _tab2, [@count1] = [@count2] ), [ArtId] )
RETURN
_t1count + _linkcount
Best Regards
Hello, @v-yiruan-msft thank you very much, it worked for the first measure, do you have any idea how can i do it for the second one?
Hi @uif19085 ,
You can create a measure as below to get it, please find the details in the attachment.
Measure2 =
VAR _t1count =
CALCULATE (
DISTINCTCOUNT ( 'Table 1'[ArtID] ),
FILTER (
'Table 1',
'Table 1'[STR] = 1
&& 'Table 1'[Implementation] = "implemented"
)
)
VAR _tab =
CALCULATETABLE (
VALUES ( 'Table 1'[ArtID] ),
FILTER ( 'Table 1', 'Table 1'[STR] = 1 )
)
VAR _tab1 =
CALCULATETABLE (
VALUES ( 'Table 1'[ArtID] ),
FILTER (
'Table 1',
'Table 1'[STR] = 1
&& 'Table 1'[Implementation] <> "implemented"
)
)
VAR _uids =
CALCULATETABLE (
VALUES ( 'Table 2'[Link] ),
FILTER ( 'Table 2', 'Table 2'[ArtId] IN _tab1 )
)
VAR _uids1 =
CALCULATETABLE (
VALUES ( 'Table 1'[UnID] ),
FILTER (
'Table 1',
'Table 1'[UnID]
IN _uids
&& 'Table 1'[Implementation] = "implemented"
&& ( 'Table 1'[SYS] = 1
|| 'Table 1'[EER] = 1 )
)
)
VAR _tab2 =
SUMMARIZE (
FILTER ( 'Table 2', 'Table 2'[ArtId] IN _tab1 ),
'Table 2'[ArtId],
"@count1", COUNT ( 'Table 2'[Link] ),
"@count2",
CALCULATE (
COUNT ( 'Table 2'[Link] ),
FILTER ( 'Table 2', 'Table 2'[Link] IN _uids1 )
)
)
VAR _linkcount =
COUNTX ( FILTER ( _tab2, [@count1] = [@count2] ), [ArtId] )
RETURN
_t1count + _linkcount
Best Regards
Hi @uif19085 ,
I created a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:
Measure =
VAR _tab1 =
CALCULATETABLE (
VALUES ( 'Table 1'[ArtID] ),
FILTER ( 'Table 1', 'Table 1'[STR] = 1 )
)
VAR _tabuid =
CALCULATETABLE (
VALUES ( 'Table 1'[UnID] ),
FILTER ( 'Table 1', 'Table 1'[SYS] = 1 || 'Table 1'[EER] = 1 )
)
VAR _tab2 =
CALCULATETABLE (
VALUES ( 'Table 2'[ArtId] ),
FILTER ( 'Table 2', 'Table 2'[Link] IN _tabuid )
)
VAR _tab3 =
INTERSECT ( _tab2, _tab1 )
RETURN
COUNTX ( _tab3, [ArtId] )
If the above one can't help you get the expected result,
please provide some raw data in the table 'Main Table' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |