Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I'm still learning and did some research into the topic. It looks like Power BI doesn't have an ifna function like excel and uses lookupvalue. I looked at those threads, but couldn't figure out how to apply it to my situation.
I'm working with City data that has a quadrant system (NW, SW, NE, and SE). The source has two sets of quadrant data. Normally I would assume they are the same and choose one, but life is just never that easy.
Note, this is a sample and not indicative of how many N/A's are in each column.
Priority
I'm assuming I will need to add a new column, but I don't know how to write the formula. It would compare "quadrant" and "geo_area_quadrant".
Add on option A (if possible)
I'm not sure if this is possible, well an easy way for the following ask... this is an issue with the raw data. In the instances where both columns are N/A, is it possible to extract data from an address column? If so, could it be combined with the column in my priority ask above, or would it be an additional column?
Thanks in advance!
Solved! Go to Solution.
Create a custom column and try this...
if List.Contains({"NW","SE","NE","SW"},[quadrant])
then [quadrant]
else if List.Contains({"NW","SE","NE","SW"},[geo_area_quadrant])
then [geo_area_quadrant]
else if Text.Contains([display_address],"NW") then "NW"
else if Text.Contains([display_address],"SW") then "SW"
else if Text.Contains([display_address],"NE") then "NE"
else if Text.Contains([display_address],"SE") then "SE"
else "who knows"
DAX for add new column:
Derived Quadrant =
SWITCH( TRUE(),
'Table'[quadrant] <> "N/A", 'Table'[quadrant],
'Table'[geo_area_quadrant] <> "N/A", 'Table'[geo_area_quadrant],
'Table'[quadrant] = "N/A" && 'Table'[geo_area_quadrant] = "N/A" ,
SWITCH( TRUE(),
CONTAINSSTRING('Table'[display_address], "SE"), "SE",
CONTAINSSTRING('Table'[display_address], "NE"), "NE",
CONTAINSSTRING('Table'[display_address], "NW"), "NW",
CONTAINSSTRING('Table'[display_address], "SW"), "SW"
-- ... add more conditions of your needs
, "N/A")
, "N/A" -- you can chose if any of the above conditions do not meet, as default value!
)
DAX for add new column:
Derived Quadrant =
SWITCH( TRUE(),
'Table'[quadrant] <> "N/A", 'Table'[quadrant],
'Table'[geo_area_quadrant] <> "N/A", 'Table'[geo_area_quadrant],
'Table'[quadrant] = "N/A" && 'Table'[geo_area_quadrant] = "N/A" ,
SWITCH( TRUE(),
CONTAINSSTRING('Table'[display_address], "SE"), "SE",
CONTAINSSTRING('Table'[display_address], "NE"), "NE",
CONTAINSSTRING('Table'[display_address], "NW"), "NW",
CONTAINSSTRING('Table'[display_address], "SW"), "SW"
-- ... add more conditions of your needs
, "N/A")
, "N/A" -- you can chose if any of the above conditions do not meet, as default value!
)
Create a custom column and try this...
if List.Contains({"NW","SE","NE","SW"},[quadrant])
then [quadrant]
else if List.Contains({"NW","SE","NE","SW"},[geo_area_quadrant])
then [geo_area_quadrant]
else if Text.Contains([display_address],"NW") then "NW"
else if Text.Contains([display_address],"SW") then "SW"
else if Text.Contains([display_address],"NE") then "NE"
else if Text.Contains([display_address],"SE") then "SE"
else "who knows"
User | Count |
---|---|
48 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
57 | |
29 | |
20 | |
16 |