Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
faustine
Frequent Visitor

Create Calculated Fields based on Field Parameter selected

Hi Everyone

 

I would like to create a calculated field that is based on what I select from Field Parameters.

I created a field parameter that contains Column A (all numbers) and Column B (all numbers).

Then I tried creating a new calculated column that should be divided by the values from Column A or B, depending on the user selection. I tried it by using DAX and Power Query, but I can't seem to get it to work.

 

How do i go about doing this? Any help will be appreciated

 

Thanks

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @faustine ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1678774285097.png

2. create a field parameter

vbinbinyumsft_1-1678774407028.png

3. create a measure with below dax formula

Measure =
VAR tmp =
    SELECTCOLUMNS ( Parameter, "SelectColumn", [Parameter] )
VAR _str =
    CONCATENATEX ( tmp, [SelectColumn] )
VAR _val =
    SWITCH (
        _str,
        "Column1", SUMX ( SELECTCOLUMNS ( 'Table', "Column1", [Column1] ), [Column1] ),
        "Column2", SUMX ( SELECTCOLUMNS ( 'Table', "Column2", [Column2] ), [Column2] )
    )
VAR _val1 = 100
RETURN
    DIVIDE ( _val, _val1 )

4. add a table visual and a card visual

Animation03.gif

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-binbinyu-msft
Community Support
Community Support

Hi @faustine ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1678774285097.png

2. create a field parameter

vbinbinyumsft_1-1678774407028.png

3. create a measure with below dax formula

Measure =
VAR tmp =
    SELECTCOLUMNS ( Parameter, "SelectColumn", [Parameter] )
VAR _str =
    CONCATENATEX ( tmp, [SelectColumn] )
VAR _val =
    SWITCH (
        _str,
        "Column1", SUMX ( SELECTCOLUMNS ( 'Table', "Column1", [Column1] ), [Column1] ),
        "Column2", SUMX ( SELECTCOLUMNS ( 'Table', "Column2", [Column2] ), [Column2] )
    )
VAR _val1 = 100
RETURN
    DIVIDE ( _val, _val1 )

4. add a table visual and a card visual

Animation03.gif

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • I have a similar situation where I have four columns that are all text columns. I have a field parameter slicer where I will need to select multiple parameters. If possible, In addition to returning the values in those 4 columns in a table visual, I am hoping to create an additional column that will concatenate the values for each row with the pipe delimiter ("|". Using the example above, column 3 would be returned 10|15 if both column 1 and 2 were selected in the parameter selection.

Hope the latter part of this video helps(from the 8 minute mark onwards) : https://www.youtube.com/watch?v=7mlYt6x-XCo&list=PLApPcvU5-R26t4dylOnPy8A2-lr4C0C--&index=31

I think I am almost there. The 4 columns I have are "computer name", "property number", "mac address", and "service tag". I am trying to build a unique key so if only the computer name is selected on the slicer, then it will return the computername value in the table. If they select only the property number in the slicer, it will only return the property number value in the table. If they select computer name and property number, it will bring back the computernamevalue|propertynamevalue in the table. If they select 3 slicer values, it will bring back all 3 of those values in the table. Every row has a computer name but the rows might not have any of the remainder columns. I have this measure working in a card to show the unique key format but I can't get it to work in the table where I need to bring in the values. 

 

Uniquekey =CONCATENATEX(VALUES(ASSETSLICER[FIELD]),ASSETSLICER[FIELD], "|")

 

The ASSETSLICER[Field] is a calculated column that I created in the slicer table.

Field=AssetSlicer[AssetSlicer]

 

That measure brings back "computername" or "computername|propertynumber", etc...

 

Any help would be greatly appreciated.

 

 

Padycosmos
Solution Sage
Solution Sage

Hope this helps:

Padycosmos_0-1678373736189.png

 

YukiK
Impactful Individual
Impactful Individual

What you're looking for is probably using a measure instead of calculated columns. Calculated columns get created at refresh time. I've never tried creating calc columns based on field parameters but no wonder if you can't

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors