topic Field Parameter Work Around in Report Server

Field Parameter Work Around

STEFAN_SA — Tue, 19 Mar 2024 07:42:51 GMT

Good Day All
I hope you can advice me on a way to use DAX to create a Field Parameter like function.

I want to use a bar or column chart to display the total counts per User based on a paramter I created.
I can add my Switch measure for counts on the x - axis but when I do something simular for the users it does not work when I add it to the y - axis.
Every google search I do points back to the Desktop version of Field Parameters But PowerBI RS does not have that function.

Data:
I have one main table with all the data. In this table i have a column with a unique number that I use for counts. I have TimeStamp columns for each process of the unique number and with the TimeStamp there is a User

I have duplicated the main Users table into the diffrent processes. UserAcknowledged, UserPhoned, UserCompleted. I created an index And then linked them to the Main Table on the individual UserID's, the TenantID's, and ClientID's

Take Note: I have another version Where I kept the UsersTable as one table but broke up the Main table in the diffrent pressess but that increased my file size alot but I was at least able to design my chart.

I created a Slicer measure that will control the counts and hopefully the users all at once

(Slicer)_OperationStages =

UNION(

SELECTCOLUMNS(

GENERATESERIES(1, 7, 1),"ID", [Value], "StageDesc",

SWITCH(

[Value], 1, "All", 2, "Acknowledged", 3, "Phoned", 4, "Completed", 5, "Canceled", 6, "Dispatched Stack", 7, "Dispatched")

ROW("ID", 0, "StageDesc", "Unknown") -- Add an "Unknown" stage with ID 0 if needed

)

When I create the Switch function measure for users it does not allow it to pined to the bar chart visual

_Exp.Switch.All(Users) =

VAR SelectedStageID = SELECTEDVALUE(('(Slicer)_OperationStages'[ID]))

RETURN

SWITCH (

SelectedStageID,

2, SELECTEDVALUE('dimUsers(Ack)'[Username]),

3, SELECTEDVALUE('dimUsers(Phn)'[Username]),

4, SELECTEDVALUE('dimUsers(Com)'[Username]),

5, SELECTEDVALUE('dimUsers(Can)'[Username]),

7, SELECTEDVALUE('dimUsers(Disp)'[Username]),

BLANK() -- Return blank if no selection or unexpected ID

)

Is there any advice that someone can provide to help me.

Re: Field Parameter Work Around

Anonymous — Wed, 20 Mar 2024 05:20:13 GMT

Hi STEFAN_SA ,

It seems that you want to put the measure on the axis.

I have found a document about using the measure as axis in the visual. Please refer to it.

The workaround of using the measure as axis in the... - Microsoft Fabric Community

Or Instead of trying to dynamically switch the user dimension on the y-axis, use your slicer to dynamically adjust the measure (counts) displayed on the x-axis. This measure can be something like:

Dynamic User Counts = VAR SelectedStageID = SELECTEDVALUE(('(Slicer)_OperationStages'[ID])) RETURN SWITCH( SelectedStageID, 1, [TotalCountsAll], -- Assuming [TotalCountsAll] is a measure for all stages 2, [TotalCountsAcknowledged], 3, [TotalCountsPhoned], -- Add other cases as necessary BLANK() -- Default or error case )

Visual Setup: Use your users dimension on the y-axis and the measure on the x-axis of your bar chart. This setup should allow the counts to dynamically adjust based on the slicer selection, while consistently displaying users on the y-axis.Dynamic User Counts

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Field Parameter Work Around

STEFAN_SA — Wed, 20 Mar 2024 05:43:39 GMT

Anonymous
Thank you for the feedback

As you mentioned I already have the "Switch" Measure for counts.
My main goal was that I wanted to create a "User Stats" look where by I am highliting the Top performers by what ever stage their in.
Using a visual like a bar chart And the (Slicer)_OperationStages to switch the counts and users based on the diffrent stages.
Example: Acknowlaged Counts by all Users Who Acknowlaged a Signal. Or Phoned Counts by All Users Who Phoned

_Exp.Switch.All(Counts) =

VAR SelectedStageID = SELECTEDVALUE(('(Slicer)_OperationStages'[ID]))

RETURN

--[Value], 1, "All", 2, "Acknowledged", 3, "Phoned", 4, "Completed", 5, "Canceled", 6, "Dispatched Stack", 7, "Dispatched")

SWITCH(

SelectedStageID,

2, [_Exp.Count.TotalOB (ACK)],

3, [_Exp.Count.TotalOB (PHN)],

4, [_Exp.Count.TotalOB (COM)],

5, [_Exp.Count.TotalOB (CAN)],

6, [_Exp.Count.TotalOB (DISP.STK)],

7, [_Exp.Count.TotalOB (DISP)],

BLANK() -- Return blank if no selection or unexpected ID

)

I Think my issue I have at this point is Between the two tables. Main Table and the User table.
As stated that the main table have the UsersID's for each stage and you can only have the user table link to one of those users.

When I split the Main table into the diffrent stages and using one User table I did not have any issue appoart from the file size that was too big

So now I am trying to duplicate the user table for each stage to link or merge to the main table.

But I am still faced with the "Switch" measure that does not link to the visual.
I am checking you article now, and see its kind of what I did with the measure switch on counts per User.
As mentioned above I need to switch both x-Axis the counts of users and the y-Axis users based on the operation stage that they fall into

Does this make sence? how do I share the file?

Re: Field Parameter Work Around

Anonymous — Wed, 20 Mar 2024 06:07:07 GMT

Hi STEFAN_SA ,

Please provide more details.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Field Parameter Work Around

STEFAN_SA — Wed, 20 Mar 2024 07:14:06 GMT

Anonymous
https://youtu.be/fNzbddt7t2E

Re: Field Parameter Work Around

STEFAN_SA — Wed, 20 Mar 2024 08:04:35 GMT

Anonymous
Below a sample file as well. had to remove as much as i could but still try to keep what is needed. Aslo to reduce the size from 303MB

https://drive.google.com/file/d/1vtYw55lVM5dOBrc6RT0W5q-sAoB9LAGo/view?usp=sharing