Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |