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.
Greetings,
I'm struggling to find a solution to my problem.
I have two related tables as following:
I'm trying to calculate this measure that i need to insert inside a table visual.
var a = MAX(Table1[Durata])
var b = MAX(Table2[soglie])
return
SWITCH(TRUE,
a > b , -1,
a < b , 1,
0)
This is the table, as you can see the relation between Table1.key and Table2.key is working as expected.
And this is the result adding the measure.
What exactly I'm not understanding? Of course this is related to switch and if function..... but I'm not getting it.
Any help would be much appreciated
Solved! Go to Solution.
There must be a better way of doing this, but the below seems to work
Measure =
var Table2Keys = SUMMARIZE( Table1, Table2[_kEY])
var CurrentKey = SELECTEDVALUE(Table1[_kEY])
RETURN IF( CurrentKey IN Table2Keys,
var a = MAX(Table1[Durata])
var b = MAX(Table2[soglie])
return
SWITCH(TRUE,
a > b , -1,
a < b , 1,
0)
)
Having a bidirectional many-to-one relationship is usually a bad idea, try changing it to a single direction if you can.
i already try:
and this does not change the result....
if in the measure i m not using the switch or if function, it's working properly.
Any idea??
Can you copy the code generated for the table visual from Performance Analyzer and paste it in here? Or can you share a PBIX file with any confidential info removed ?
// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'Table1'[_kEY],
'Table1'[Durata],
'Table2'[soglie],
'Table2'[_kEY],
'Table1'[Operazione],
'Table1'[Processo]
), "IsGrandTotalRowTotal"
),
"Measure2", 'Table1'[Measure2]
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'Table1'[_kEY],
1,
'Table1'[Durata],
1,
'Table2'[soglie],
1,
'Table2'[_kEY],
1,
'Table1'[Operazione],
1,
'Table1'[Processo],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
'Table1'[_kEY],
'Table1'[Durata],
'Table2'[soglie],
'Table2'[_kEY],
'Table1'[Operazione],
'Table1'[Processo]
There must be a better way of doing this, but the below seems to work
Measure =
var Table2Keys = SUMMARIZE( Table1, Table2[_kEY])
var CurrentKey = SELECTEDVALUE(Table1[_kEY])
RETURN IF( CurrentKey IN Table2Keys,
var a = MAX(Table1[Durata])
var b = MAX(Table2[soglie])
return
SWITCH(TRUE,
a > b , -1,
a < b , 1,
0)
)
It's working ...
the thing that bothers me the most is not understanding the behavior of my measure.
The problem lay in the behaviour of SUMMARIZECOLUMNS and the fact that your measure was not returning blank for invalid combinations of rows from table 1 and table 2.
I don't fully understand the workings of SUMMARIZECOLUMNS, but it looks like it was returning all possible combinations of rows from table 1 & 2, rather than using the relationship, but it will only return combinations where the measures being summarized do not return a blank.
The code I wrote seeks to establish whether the current combination is a valid one, given the relationship, and only return a value in the case of valid combinations.
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |