Recreate an Excel table in Power BI

[DmitryD77]

Hello community.

Please help me recreate the Excel table in Power BI.

Expected result:

Link to sample file.

Thanks in advance.

[DmitryD77]

@aduguid thank you!

[aduguid]

no worries, anytime, happy to help

[aduguid · Accepted solution]

First I would create a calculated column in "fct_test_results".

Rank = 
RANKX(
    FILTER(
        fct_test_results,
        fct_test_results[Course] = EARLIER(fct_test_results[Course])
    )
    , fct_test_results[Pace],
    , ASC
)

Then you can create a DAX query for the results.

fct_test_results_pivot = 

VAR _result = 
UNION(
	SELECTCOLUMNS(fct_test_results
	, "Course"		    , fct_test_results[Course]
    , "Rank"		    , fct_test_results[Rank]
	, "Column Value"	, fct_test_results[Pace]
	, "Column Name"	    , "Pace")
,
	SELECTCOLUMNS(fct_test_results
	, "Course"		    , fct_test_results[Course]
    , "Rank"		    , fct_test_results[Rank]
	, "Column Value"	, FORMAT(fct_test_results[Pace Test Result], "0%")
	, "Column Name"	    , "Result")
, 
	SELECTCOLUMNS(fct_test_results
	, "Course"		    , fct_test_results[Course]
    , "Rank"		    , fct_test_results[Rank]
	, "Column Value"	, FORMAT(fct_test_results[Pace Test Date], "MM/dd/yyyy")
	, "Column Name"	    , "Date")
)

RETURN
_result

If you want a different sort, you can create an additional DAX query and then relate it to the pivot.

Metric List = 
VAR _results = 
DATATABLE (
      "Column Name",   STRING
	, "Column Order",  INTEGER
	,
    {
          {"Pace",      1}
		, {"Result",    2}
		, {"Date",	    3}
    }
)

RETURN
_results

In this matrix I've used the [Course] from the "fct_test_results_pivot", [Column Name] from "Metric List" (which has a column sort on [Column Order]), [Rank] from "fct_test_results_pivot" and the first [Column Value] from "fct_test_results_pivot". If you have a unique key/id, you can include it in the pivot and relate it back to your original table so date filters would still work.