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 everyone,
I got a table with risks that looks like the below
Organisation | Business | Customer | InfoSec | Emerging |
Organisation 1 | Business 1 | 1 | 2 | |
Organisation 1 | Business 2 | 2 | 3 | 3 |
Organisation 2 | Business 1 | 3 | 3 | 3 |
Organisation 2 | Business 2 | 1 | 1 | 1 |
Organisation 2 | Business 3 | 2 | 2 |
I need to run an analysis in which in a visual or even to the table to check if the value placed across the different risks for each combination of organisation and business is equal. Moreover, I need to not take into account any null values. If only one value applicable in the combination then, just N/A.
The outcome in a table could look like the below:
Organisation | Business | Customer | InfoSec | Emerging | Compare |
Organisation 1 | Business 1 | 1 | 2 | FALSE | |
Organisation 1 | Business 2 | 2 | 3 | 3 | FALSE |
Organisation 2 | Business 1 | 3 | 3 | 3 | TRUE |
Organisation 2 | Business 2 | 1 | N/A | ||
Organisation 2 | Business 3 | 2 | 2 | TRUE |
Any ideas?
Thanks in advance
Solved! Go to Solution.
HI @Kostas ,
Based on the expanation you mentioned, I got the following DAX: (I have added explanation at every condition checked in the DAX expression)
Compare =
var cust = SELECTEDVALUE(orgData[Customer])
var emg = SELECTEDVALUE(orgData[Emerging])
var info = SELECTEDVALUE(orgData[InfoSec])
RETURN
IF(
cust = emg && cust = info && emg = info, "True", //checkng if all columns have same value
IF(
cust = BLANK() && emg = info, "True", // checking if one of columns is Blank and rest of the 2 have same value
IF(
emg = BLANK() && cust = info, "True", // checking if one of the 3 columns is Blank and rest of the 2 have same value
IF(
info = BLANK() && emg = cust, "True", // checking if one of the 3 columns is Blank and rest of the 2 have same value
IF(
cust = BLANK() && emg = BLANK() && info = BLANK(), "N/A", // checking if all the 3 columns are Blank
IF(
cust = BLANK() && emg = BLANK(), "N/A", // checking if any of the 2 columns are Blank
IF(
emg = BLANK() && info = BLANK(), "N/A", // checking if any of the 2 columns are Blank
IF(
cust = BLANK() && info = BLANK(), "N/A", // checking if any of the 2 columns are Blank
"False" // remaining condition gets False which means no value is same in any of the 3 columns
)
)
)
)
)
)
)
)
When I move it to the table visual:
It seems to align with your explanation. Let me know if this is what you are looking for.
Thanks,
Pragati
Hi @Kostas ,
Can you explain the logic on how you derived the Compare column in the second screenshot of your's?
Your explanation is not enough to understand what is the logic here.
Thanks,
Pragati
Hello and thanks for the reply,
I am basically comparing the three columns Customer, InfoSec and emerging.
If the number between the three columns is the same then the last column should return TRUE, if not then FALSE. In case that only one of the three columns has only value then to return N/A.
Also, the null/blank values should be excluded from the comparison.
For example:
Emerging = null
Customer = 3
InfoSec = 3
Compare column = TRUE (as the customer and InfoSec are the same)
Example 2:
Customer = 2
InfoSec = 2
Emerging = 1
Compare column = False.
If all three columns are null then null.
The comparison should always run between the values that have the same Organisation and Business.
I hope that helps, let me know and I can create a table with more exmaples.
In excel for example you can write =AND(B2 = B3:B4) but still is considering blank/null values
HI @Kostas ,
Based on the expanation you mentioned, I got the following DAX: (I have added explanation at every condition checked in the DAX expression)
Compare =
var cust = SELECTEDVALUE(orgData[Customer])
var emg = SELECTEDVALUE(orgData[Emerging])
var info = SELECTEDVALUE(orgData[InfoSec])
RETURN
IF(
cust = emg && cust = info && emg = info, "True", //checkng if all columns have same value
IF(
cust = BLANK() && emg = info, "True", // checking if one of columns is Blank and rest of the 2 have same value
IF(
emg = BLANK() && cust = info, "True", // checking if one of the 3 columns is Blank and rest of the 2 have same value
IF(
info = BLANK() && emg = cust, "True", // checking if one of the 3 columns is Blank and rest of the 2 have same value
IF(
cust = BLANK() && emg = BLANK() && info = BLANK(), "N/A", // checking if all the 3 columns are Blank
IF(
cust = BLANK() && emg = BLANK(), "N/A", // checking if any of the 2 columns are Blank
IF(
emg = BLANK() && info = BLANK(), "N/A", // checking if any of the 2 columns are Blank
IF(
cust = BLANK() && info = BLANK(), "N/A", // checking if any of the 2 columns are Blank
"False" // remaining condition gets False which means no value is same in any of the 3 columns
)
)
)
)
)
)
)
)
When I move it to the table visual:
It seems to align with your explanation. Let me know if this is what you are looking for.
Thanks,
Pragati
Hi,
That would work, I was just hoping for a better solution and easier solution.
In case that I got more than 3 columns then it becomes to complicated.
Thanks for the answer that would work for now.
Thanks
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |