Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, everyone. I need some help.
I have a database called "Base" with some columns:
1 - ID
2 - Company: Company name
3 - Origin: List of origin cities
4 - Destination: List of destination cities
5 - Amount(kg): Amount of Kg transported
(Example - Base database)
I want to create a table as follows:
(Desired visual - Example)
I have tried the code below, but without success.
Destino Mais Utilizado =
VAR DestinoMaisUtilizado =
TOPN(1,
SUMMARIZE('Base', 'Base'[Destination], "Total KG Carga Paga", SUM('Base'[Amount (kg)])),[Total KG Carga Paga], DESC)
RETURN
SELECTCOLUMNS(DestinoMaisUtilizado, "Destino Mais Utilizado", 'Base'[Destination])
Thanks!
Solved! Go to Solution.
Hi, I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Destination topN table =
VAR _rowcount =
ADDCOLUMNS (
VALUES ( Base[Company] ),
"@rowcount", CALCULATE ( COUNTROWS ( Base ) )
)
VAR _maxrowcount =
MAXX ( _rowcount, [@rowcount] )
VAR _createNtable =
ADDCOLUMNS (
ADDCOLUMNS ( GENERATESERIES ( 1, _maxrowcount, 1 ), "@Top", "Top " ),
"@metricsname", [@Top] & [Value]
)
VAR _addcompanyname =
GENERATE ( Base, _createNtable )
VAR _addresultcolumn =
FILTER (
ADDCOLUMNS (
_addcompanyname,
"@result",
CALCULATE (
SUM ( Base[Amount (kg)] ),
WINDOW (
[Value],
ABS,
[Value],
ABS,
FILTER ( Base, Base[Company] = EARLIER ( Base[Company] ) ),
ORDERBY ( CALCULATE ( SUM ( Base[Amount (kg)] ) ), DESC ),
,
,
MATCHBY ( Base[ID] )
)
)
),
Base[Amount (kg)] = [@result]
)
RETURN
SUMMARIZE (
_addresultcolumn,
Base[ID],
Base[Company],
Base[Destination],
[@metricsname],
[@result]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Destination topN table =
VAR _rowcount =
ADDCOLUMNS (
VALUES ( Base[Company] ),
"@rowcount", CALCULATE ( COUNTROWS ( Base ) )
)
VAR _maxrowcount =
MAXX ( _rowcount, [@rowcount] )
VAR _createNtable =
ADDCOLUMNS (
ADDCOLUMNS ( GENERATESERIES ( 1, _maxrowcount, 1 ), "@Top", "Top " ),
"@metricsname", [@Top] & [Value]
)
VAR _addcompanyname =
GENERATE ( Base, _createNtable )
VAR _addresultcolumn =
FILTER (
ADDCOLUMNS (
_addcompanyname,
"@result",
CALCULATE (
SUM ( Base[Amount (kg)] ),
WINDOW (
[Value],
ABS,
[Value],
ABS,
FILTER ( Base, Base[Company] = EARLIER ( Base[Company] ) ),
ORDERBY ( CALCULATE ( SUM ( Base[Amount (kg)] ) ), DESC ),
,
,
MATCHBY ( Base[ID] )
)
)
),
Base[Amount (kg)] = [@result]
)
RETURN
SUMMARIZE (
_addresultcolumn,
Base[ID],
Base[Company],
Base[Destination],
[@metricsname],
[@result]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
102 | |
93 | |
73 | |
60 | |
59 |