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.
Hello,
How can I create Parameters for Correct Date and Segmentt with the "treat as" statements in my query.
I am trying to bring this DAX statement from my power BI desktop to power bi report builder query designer.
// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('data ControllerLog'[Nature Of Call])),
NOT(ISBLANK('data ControllerLog'[Nature Of Call]))
)
VAR __DS0FilterTable2 =
TREATAS({DATE(2013, 1, 1)}, 'data TOSAIncident'[Correct date])
VAR __DS0FilterTable3 =
TREATAS({"CenterCity"}, 'def TrolleySegments'[Segment Name])
VAR __DS0Core =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'data TOSAIncident'[Reported By],
'data ControllerLog'[Nature Of Call],
'data ControllerLog'[Action Taken],
'data TOSAIncident'[Occurrence Time Stamp],
'def TrolleySegments'[Segment Name],
'v_CombinedLocationSegments'[Location Description],
'data TOSAIncident'[Correct date],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"Color_Code", IGNORE('def TrolleySegments'[Color Code]),
"CountRowsdata_TOSAIncident", CALCULATE(COUNTROWS('data TOSAIncident')),
"CountRowsdata_ControllerLog", CALCULATE(COUNTROWS('data ControllerLog'))
)
),
OR(
OR(
OR(
OR(
OR(
OR(
NOT(ISBLANK('data TOSAIncident'[Reported By])),
NOT(ISBLANK('data ControllerLog'[Nature Of Call]))
),
NOT(ISBLANK('data ControllerLog'[Action Taken]))
),
NOT(ISBLANK('data TOSAIncident'[Occurrence Time Stamp]))
),
NOT(ISBLANK('def TrolleySegments'[Segment Name]))
),
NOT(ISBLANK('v_CombinedLocationSegments'[Location Description]))
),
NOT(ISBLANK('data TOSAIncident'[Correct date]))
)
)
),
"'data TOSAIncident'[Reported By]", 'data TOSAIncident'[Reported By],
"'data ControllerLog'[Nature Of Call]", 'data ControllerLog'[Nature Of Call],
"'data ControllerLog'[Action Taken]", 'data ControllerLog'[Action Taken],
"'data TOSAIncident'[Occurrence Time Stamp]", 'data TOSAIncident'[Occurrence Time Stamp],
"'def TrolleySegments'[Segment Name]", 'def TrolleySegments'[Segment Name],
"'v_CombinedLocationSegments'[Location Description]", 'v_CombinedLocationSegments'[Location Description],
"'data TOSAIncident'[Correct date]", 'data TOSAIncident'[Correct date],
"Color_Code", [Color_Code]
)
VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0Core,
'data TOSAIncident'[Occurrence Time Stamp],
0,
'data TOSAIncident'[Reported By],
1,
'data ControllerLog'[Nature Of Call],
1,
'data ControllerLog'[Action Taken],
1,
'def TrolleySegments'[Segment Name],
1,
'v_CombinedLocationSegments'[Location Description],
1,
'data TOSAIncident'[Correct date],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
'data TOSAIncident'[Occurrence Time Stamp] DESC,
'data TOSAIncident'[Reported By],
'data ControllerLog'[Nature Of Call],
'data ControllerLog'[Action Taken],
'def TrolleySegments'[Segment Name],
'v_CombinedLocationSegments'[Location Description],
'data TOSAIncident'[Correct date]
Create your datasource and then go into the Query Designer to create your dataset, paste your DAX, create your two parameters with the button in the ribbon, and then update your DAX with those names with @.
VAR __DS0FilterTable2 =
TREATAS({@DateParam}, 'data TOSAIncident'[Correct date])
VAR __DS0FilterTable3 =
TREATAS({@CityParam}, 'def TrolleySegments'[Segment Name])
I got an error at first when I tried it a different way, but it worked when I used the designer.
Pat