Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have the following table of data:
Soft1 Company | Soft1 Computer Name | Soft1 Version | Soft2 Version | Soft2 Policy | Soft2 Company | Soft2 Computer Name |
#N/A | #N/A | RightWing | #N/A | |||
#N/A | #N/A | LeftWing | #N/A | |||
#N/A | #N/A | VeganWings | #N/A | |||
Water Bottles | AQUAFINA | 12.65 | 1.223 | Protect | Watter Bottles | AQUAFINA |
Water Bottles | DASANI | 12.6 | 1.554 | Protect & Defend | Watter Bottles | DASANI |
Tabletops | MARBLE | 11.44 | 1.764 | Protect & Defend | Tabletops | MARBLE |
Tabletops | GRANITE | 12.6 | 1.554 | Protect | Tabletops | GRANITE |
Hoodies | COTTON | 11.44 | 1.223 | Protect & Defend | Hoodies | COTTON |
I merge the "Soft1 Company"+"Soft2 Company" and "Soft1 Computer Name"+"Soft2 Computer Name" columns into 2 new columns (called "Company" & "Computer Name") with the calculated measures below:
Company =
VAR Client1 = SELECTEDVALUE('Sample'[Soft1 Company])
VAR Client2 = SELECTEDVALUE('Sample'[Soft2 Company])
RETURN SWITCH(
TRUE(),
EXACT(Client1, Client2), Client1,
EXACT(Client1, ""), Client2,
EXACT(Client2, ""), Client1,
Client1
)
Computer Name =
VAR Comp1 = SELECTEDVALUE('Sample'[Soft1 Computer Name])
VAR Comp2 = SELECTEDVALUE('Sample'[Soft2 Computer Name])
RETURN SWITCH(
TRUE(),
EXACT(Comp1, Comp2), Comp1,
EXACT(Comp1, ""), Comp2,
EXACT(Comp2, ""), Comp1,
Comp1
)
Then I want to hide the "Soft1 Company"+"Soft2 Company" and "Soft1 Computer Name"+"Soft2 Computer Name" columns so only the following data shows:
Company | Computer Name | Soft1 Version | Soft2 Version | Soft2 Policy |
RightWing | #N/A | #N/A | #N/A | |
LeftWing | #N/A | #N/A | #N/A | |
VeganWings | #N/A | #N/A | #N/A | |
Water Bottles | AQUAFINA | 12.65 | 1.223 | Protect |
Water Bottles | DASANI | 12.6 | 1.554 | Protect & Defend |
Tabletops | MARBLE | 11.44 | 1.764 | Protect & Defend |
Tabletops | GRANITE | 12.6 | 1.554 | Protect |
Hoodies | COTTON | 11.44 | 1.223 | Protect & Defend |
However, I am forced to include the unnecessary Soft2 Company column for the above data to show, or else the 3 rows with "#N/A" data are merged and the Company column data disappears like this:
Company | Computer Name | Soft1 Version | Soft2 Version | Soft2 Policy |
#N/A | #N/A | #N/A | ||
Water Bottles | AQUAFINA | 12.65 | 1.223 | Protect |
Water Bottles | DASANI | 12.6 | 1.554 | Protect & Defend |
Tabletops | MARBLE | 11.44 | 1.764 | Protect & Defend |
Tabletops | GRANITE | 12.6 | 1.554 | Protect |
Hoodies | COTTON | 11.44 | 1.223 | Protect & Defend |
The odd thing is, the Filters options still indicate that all 3 "#N/A" data rows are visible when they aren't.
That probably doesn't make sense, so here's a video demonstrating the rows merging when I deselect the Soft2 Company column:
I thought selecting the columns in the Fields area was just selecting which columns you want visible, and the columns visible shouldn't affect the dataset itself.
Can someone help me fix the dataset so rows don't disappear when columns are deselected? I'm sure there's a very obvious fault in my understanding/logic but I would appreciate someone kindly tell me how to avoid this. Thank you!
DisappearingRows.pbix download:
https://drive.google.com/file/d/1Z_P25Hfc_C6pTgC_k6jtS28LhVm0JJtj/view?usp=sharing
Solved! Go to Solution.
I'll try and explain it.
Powerbi treats rows of the same data as duplicates (when they are in a visual). This is because powerbi is all about aggregations.
Measures are evaluated in context i.e. what data is in filters, what data is in rows .
So, without the addition of Soft2 Company in the visual the Company measure has no context to return a value therefore powerbi sees the 3 rows as duplicates and shows them as one.
I'm pretty sure you can solve this by creating calculated columns rather than measures - this makes sense since the values are evaluated by row anyway. You could even create the new columns in Power Query.
I'll try and explain it.
Powerbi treats rows of the same data as duplicates (when they are in a visual). This is because powerbi is all about aggregations.
Measures are evaluated in context i.e. what data is in filters, what data is in rows .
So, without the addition of Soft2 Company in the visual the Company measure has no context to return a value therefore powerbi sees the 3 rows as duplicates and shows them as one.
I'm pretty sure you can solve this by creating calculated columns rather than measures - this makes sense since the values are evaluated by row anyway. You could even create the new columns in Power Query.
Sweet - Like I assumed,
I'm sure there's a very obvious fault in my understanding/logic
I just had to remove the `SELECTEDVALUE()` functions but was otherwise able to just copy-paste my Calculated Measures to Calculated Columns instead and it seems to have worked as intended. Thank you
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |