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 another thing i cannot solve by myself.
i want to have my measures show the correct values in the correct columns and lines.
i want to show percentages with the base and the Ns filtered by brands and waves in a matrix.
but i dont know how to do it.
i have this matrix:
1. in the upper red rectangle (F5_lbl: 10+ and wave 3 and B2) i want to see the base of 478 too and N of 0 and percentage of 0%.
(set the N measure to +0 doesnt help)
2. and in the lower red rectangle i dont want to see the base.
(i know i can filter the visual by F5_lbl no empty rows.. so that the problem at the lower rectangle would be gone... but i want my measure doing this automatically).
thanks for your answers 🙂
Solved! Go to Solution.
Hi @elaj
Step 1 - create a dimension table for F5_lbl
Lbl = FILTER(SUMMARIZE(data, data[F5_lbl], data[F5_lbl_sort]), not(ISBLANK([F5_lbl])))
We keep F5_lbl_sort with F5_lbl so you can sort the new table/column
Step 2 - Make a relationship between Lbl and data
Step 3 - replace data[F5_lbl] with Lbl[F5_Lbl] in the rows of the matrix
Step 4, alter the measures as follows
Base (W) =
VAR __f5 =
IF ( HASONEVALUE ( Lbl[F5_lbl] ), SELECTEDVALUE ( Lbl[F5_lbl] ) )
RETURN
IF (
__f5 <> "",
CALCULATE ( SUM ( data[weight] ), //REMOVING THIS PART -> data[F5_lbl] <> BLANK(),
ALLNOBLANKROW ( Lbl ) ),
BLANK ()
)
For Base we remove the "data[F5_lbl] <> BLANK() as this is now handled by the model. The VAR and IF are to remove the blank rows at the end of the visual
N (W) =
VAR __f5 =
IF ( HASONEVALUE ( Lbl[F5_lbl] ), SELECTEDVALUE ( Lbl[F5_lbl] ) )
RETURN
IF ( __f5 <> "", CALCULATE ( SUM ( data[weight] ) ) +0 )
//, KEEPFILTERS(data[F5_lbl] <> BLANK()))
For N we remove KEEPFILTERS as this is now handled by the model. The VAR and IF are to remove the blank rows at the end of the visual
Now, it did add B6 to the report, but if this is a problem we should be able to work around that.
Hope this helps
David
Hi @elaj
Step 1 - create a dimension table for F5_lbl
Lbl = FILTER(SUMMARIZE(data, data[F5_lbl], data[F5_lbl_sort]), not(ISBLANK([F5_lbl])))
We keep F5_lbl_sort with F5_lbl so you can sort the new table/column
Step 2 - Make a relationship between Lbl and data
Step 3 - replace data[F5_lbl] with Lbl[F5_Lbl] in the rows of the matrix
Step 4, alter the measures as follows
Base (W) =
VAR __f5 =
IF ( HASONEVALUE ( Lbl[F5_lbl] ), SELECTEDVALUE ( Lbl[F5_lbl] ) )
RETURN
IF (
__f5 <> "",
CALCULATE ( SUM ( data[weight] ), //REMOVING THIS PART -> data[F5_lbl] <> BLANK(),
ALLNOBLANKROW ( Lbl ) ),
BLANK ()
)
For Base we remove the "data[F5_lbl] <> BLANK() as this is now handled by the model. The VAR and IF are to remove the blank rows at the end of the visual
N (W) =
VAR __f5 =
IF ( HASONEVALUE ( Lbl[F5_lbl] ), SELECTEDVALUE ( Lbl[F5_lbl] ) )
RETURN
IF ( __f5 <> "", CALCULATE ( SUM ( data[weight] ) ) +0 )
//, KEEPFILTERS(data[F5_lbl] <> BLANK()))
For N we remove KEEPFILTERS as this is now handled by the model. The VAR and IF are to remove the blank rows at the end of the visual
Now, it did add B6 to the report, but if this is a problem we should be able to work around that.
Hope this helps
David
Wow @dedelman_clng . Thanks for this detailed answer. Yes this helps. But yea. If one Brand has no base.. so no probands had the chance to answer, the zero in B6 is not really true. it should be "missing"... in power bi it should be blank. You said there is a way to work that out?
And i have another question: why is b6 now only visible in wave 3?
Hi @elaj
B6 got "added" because it is a value for "Brand" in the main table, and so it is in the Lbl dimension table. It is on the chart because N is 0.
To remove the zero from B6, change the code for N(W) to
N (W) =
VAR __f5 =
IF ( HASONEVALUE ( Lbl[F5_lbl] ), SELECTEDVALUE ( Lbl[F5_lbl] ) )
RETURN
IF (
NOT ( ISBLANK ( [Base (W)] ) ), //<-- Added this IF stmt
IF ( __f5 <> "", CALCULATE ( SUM ( data[weight] ) ) + 0 )
)
Now you will no longer see B6 in the chart. If you do, however, want to see the empty column, click on F5_Brand_OC_Top in the fields window and choose "Show items with no data"
Hope this helps
David
thanks. this solves my problem.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |