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.
I want to create table visual with multiple column from a single field
Eg: from the below table
I want distict value from day field for each status in Multiple columns.
table visual should looks like below
Please help. Thanks in Advance
Solved! Go to Solution.
Hi @Anonymous,
You can use the following calculated table expression to group your table records into different category columns based on 'status' and 'day' fields:
NewTable =
FILTER (
SELECTCOLUMNS (
GENERATESERIES ( 1, 7, 1 ),
"Index", [Value],
"Full",
LOOKUPVALUE (
'Table'[day],
'Table'[Status], "Full",
'Table'[day], FORMAT ( [Value], "dddd" ),
BLANK ()
),
"Half",
LOOKUPVALUE (
'Table'[day],
'Table'[Status], "Half",
'Table'[day], FORMAT ( [Value], "dddd" ),
BLANK ()
),
"Quarter",
LOOKUPVALUE (
'Table'[day],
'Table'[Status], "Quarter",
'Table'[day], FORMAT ( [Value], "dddd" ),
BLANK ()
)
),
[Full] & [Half] & [Quarter]
<> BLANK ()
)
Regards,
Xiaoxin Sheng
@Anonymous , Add an index column in power query
Power Query- Index Column: https://youtu.be/NS4esnCDqVw
Create this new column in DAX
Row = countx(filter(Table, [Status] = earlier([Status]) && [Index] <= earlier([Index]) ) , [Index])
Create a Matrix, Use Row on Row , Status on Column and Max of day as value
Its working but still have a problem,
It showing repeated values in each column, need to display distinct values only.
And i couldnt find Max of Day, there are first,last,count,count(distict) options only
Hi @Anonymous,
You can use the following calculated table expression to group your table records into different category columns based on 'status' and 'day' fields:
NewTable =
FILTER (
SELECTCOLUMNS (
GENERATESERIES ( 1, 7, 1 ),
"Index", [Value],
"Full",
LOOKUPVALUE (
'Table'[day],
'Table'[Status], "Full",
'Table'[day], FORMAT ( [Value], "dddd" ),
BLANK ()
),
"Half",
LOOKUPVALUE (
'Table'[day],
'Table'[Status], "Half",
'Table'[day], FORMAT ( [Value], "dddd" ),
BLANK ()
),
"Quarter",
LOOKUPVALUE (
'Table'[day],
'Table'[Status], "Quarter",
'Table'[day], FORMAT ( [Value], "dddd" ),
BLANK ()
)
),
[Full] & [Half] & [Quarter]
<> BLANK ()
)
Regards,
Xiaoxin Sheng
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |