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.
Hi!
I have the next problem and my table looks like this one:
I need generate a measure that filter col1 by some value of Col2, Col2 == Field2 for example, and Col3 by Col2==Field1, then I want to do a except between Col1 and Col3, and finally Sum the number of distinct rows by Col1. This will be my measure, is it possible to do this?
Thx.
Solved! Go to Solution.
Hi , @carmen_romero
Here is a demo.
If help , try steps as below.
1.create two Separation table as slicer
Measure 1 = CALCULATE(DISTINCTCOUNT('Table'[Col3]),FILTER('Table','Table'[Col2]="FieldB1"))
Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[Col1]),FILTER('Table','Table'[Col2]="FieldB1"))
2.create measure "measure value" as below and put it into card visual
measure value =
VAR t1 =
CALCULATETABLE (
DISTINCT ( 'Table'[Col1] ),
FILTER ( 'Table', 'Table'[Col2] IN DISTINCT ( 'Slicer table 1'[Slicer1] ) )
)
VAR t2 =
CALCULATETABLE (
DISTINCT ( 'Table'[Col3] ),
FILTER ( 'Table', 'Table'[Col2] IN DISTINCT ( 'Slicer table2'[Slicer2] ) )
)
RETURN
SUMX ( t1, IF ( [Col1] IN t2, 1, 0 ) )
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
based on your data example what result do you expect?
Hi! thx for your response!
I need to follow this steps:
1 - Filter Col1 by value of Col2 in this case Col2 == FieldB1
2 - Filter Col3 by value of Col2 in this case Col2 == FieldB3
3- Select from result of step 1 the Col1 and the step 2 the Col3
4 - Do a except between values of both columns: Col1 except Col3
5 - Count distinct values for the except in the Col1
Thx!
Hi , @carmen_romero
Here is a demo.
If help , try steps as below.
1.create two Separation table as slicer
Measure 1 = CALCULATE(DISTINCTCOUNT('Table'[Col3]),FILTER('Table','Table'[Col2]="FieldB1"))
Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[Col1]),FILTER('Table','Table'[Col2]="FieldB1"))
2.create measure "measure value" as below and put it into card visual
measure value =
VAR t1 =
CALCULATETABLE (
DISTINCT ( 'Table'[Col1] ),
FILTER ( 'Table', 'Table'[Col2] IN DISTINCT ( 'Slicer table 1'[Slicer1] ) )
)
VAR t2 =
CALCULATETABLE (
DISTINCT ( 'Table'[Col3] ),
FILTER ( 'Table', 'Table'[Col2] IN DISTINCT ( 'Slicer table2'[Slicer2] ) )
)
RETURN
SUMX ( t1, IF ( [Col1] IN t2, 1, 0 ) )
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
114 | |
105 | |
78 | |
67 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
83 | |
70 |