Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all! How do i show the columns even there is not values in it?
This is how mine looks like:
The "NA" does not show because all of them has values either for L1 or L2, but i would like to see the column to be there even though there is no value.
My desired output:
The measure for "NA"
= if [Value]="L1" or [Value]="L2" then [Value] else "NA"
Just to add on, I tried selelecting "Show items with no data" in the values field for all but nothing changes.
Question 2: How do i show a summary for each quarter like this?
Really apreciate your time and effort!!
Solved! Go to Solution.
Hi @KainLittleCloth ,
Here I unpivot the table as well.
I suggest you to create an unrelated dim table to help calculation.
DimValue =
VAR _GENERATE1 = GENERATE(
DATATABLE(
"Month",STRING,
"Order for Month",INTEGER,
{
{"Jan",1},
{"Feb",2},
{"Mar",3},
{"Previous Summary",4}
}
),VALUES('Table'[Session]))
VAR _GENERATE2 =
GENERATE(
_GENERATE1,
DATATABLE(
"Value",STRING,
"Order for Value",INTEGER,
{
{"L1",1},
{"L2",2},
{"NA",3}
}
))
RETURN
_GENERATE2
Measure:
MonthCount(L1) =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Tenor] = MAX ( 'Table'[Tenor] )
&& 'Table'[Month] = MAX ( DimValue[Month] )
&& 'Table'[Session] = MAX ( DimValue[Session] )
&& 'Table'[Value] = "L1"
)
)
MonthCount(L2) =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Tenor] = MAX ( 'Table'[Tenor] )
&& 'Table'[Month] = MAX ( DimValue[Month] )
&& 'Table'[Session] = MAX ( DimValue[Session] )
&& 'Table'[Value] = "L2"
)
)
MEASURE =
VAR _SUML1 =
CALCULATE (
SUMX (
FILTER (
ALLSELECTED ( DimValue ),
DimValue[Session] = MAX ( DimValue[Session] )
&& DimValue[Value] = "L1"
),
[MonthCount(L1)]
)
)
VAR _SUML2 =
CALCULATE (
SUMX (
FILTER (
ALLSELECTED ( DimValue ),
DimValue[Session] = MAX ( DimValue[Session] )
&& DimValue[Value] = "L2"
),
[MonthCount(L2)]
)
)
RETURN
IF (
MAX ( DimValue[Month] ) IN VALUES ( 'Table'[Month] ),
SWITCH (
MAX ( DimValue[Value] ),
"L1", [MonthCount(L1)],
"L2", [MonthCount(L2)],
"NA", IF ( [MonthCount(L1)] = [MonthCount(L2)], [MonthCount(L1)], BLANK () )
),
SWITCH (
MAX ( DimValue[Value] ),
"L1", _SUML1,
"L2", _SUML2,
"NA", IF ( _SUML1 = _SUML2, _SUML1, BLANK () )
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can you post sample data as text and expected output?
Not enough information to go on;
please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/
Hi @VahidDM ,
can i send u a link to my older post? It may have more content. Thanks for your help!
Solved: Re: Creating tables from a dataset - Microsoft Fabric Community
Can you post sample data as text here? or share PBIX file after removing sensetive data?
@VahidDM , sure!
Below is my dataset sample:
This is my desired output:
This is what i achieved so far:
1. I want to show the "NA" column even though it is empty.
2. I want a summary for each quarter.
Many thanks!
How big is your data? 1b rows, 1m rows, 1000 rows?
Do you want it blank or "-" or Space would be ok?
My data is around 400 rows only but it is still adding as days goes by. I want it blank. Thank you!
Hi @KainLittleCloth ,
Here I unpivot the table as well.
I suggest you to create an unrelated dim table to help calculation.
DimValue =
VAR _GENERATE1 = GENERATE(
DATATABLE(
"Month",STRING,
"Order for Month",INTEGER,
{
{"Jan",1},
{"Feb",2},
{"Mar",3},
{"Previous Summary",4}
}
),VALUES('Table'[Session]))
VAR _GENERATE2 =
GENERATE(
_GENERATE1,
DATATABLE(
"Value",STRING,
"Order for Value",INTEGER,
{
{"L1",1},
{"L2",2},
{"NA",3}
}
))
RETURN
_GENERATE2
Measure:
MonthCount(L1) =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Tenor] = MAX ( 'Table'[Tenor] )
&& 'Table'[Month] = MAX ( DimValue[Month] )
&& 'Table'[Session] = MAX ( DimValue[Session] )
&& 'Table'[Value] = "L1"
)
)
MonthCount(L2) =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Tenor] = MAX ( 'Table'[Tenor] )
&& 'Table'[Month] = MAX ( DimValue[Month] )
&& 'Table'[Session] = MAX ( DimValue[Session] )
&& 'Table'[Value] = "L2"
)
)
MEASURE =
VAR _SUML1 =
CALCULATE (
SUMX (
FILTER (
ALLSELECTED ( DimValue ),
DimValue[Session] = MAX ( DimValue[Session] )
&& DimValue[Value] = "L1"
),
[MonthCount(L1)]
)
)
VAR _SUML2 =
CALCULATE (
SUMX (
FILTER (
ALLSELECTED ( DimValue ),
DimValue[Session] = MAX ( DimValue[Session] )
&& DimValue[Value] = "L2"
),
[MonthCount(L2)]
)
)
RETURN
IF (
MAX ( DimValue[Month] ) IN VALUES ( 'Table'[Month] ),
SWITCH (
MAX ( DimValue[Value] ),
"L1", [MonthCount(L1)],
"L2", [MonthCount(L2)],
"NA", IF ( [MonthCount(L1)] = [MonthCount(L2)], [MonthCount(L1)], BLANK () )
),
SWITCH (
MAX ( DimValue[Value] ),
"L1", _SUML1,
"L2", _SUML2,
"NA", IF ( _SUML1 = _SUML2, _SUML1, BLANK () )
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
83 | |
82 | |
67 | |
49 |
User | Count |
---|---|
135 | |
111 | |
100 | |
65 | |
62 |