Dynamic Measures/Titles (Using Parameter Table)

3767 Views
Highlighted
Advisor
Posts: 24
Registered: ‎06-17-2017

Dynamic Measures/Titles (Using Parameter Table)

Where you have a single numeric field (e.g. 'Value') in your table though each row represents a different measure name (and/or unit for example) then you can create a dynamic measure to be displayed in your report.

 

You will need to create a seperate table with a single column (e.g. through 'Enter Data' in the Query Editor) containing just the unique list of measure names, and use this field as a slicer.

 

In the example attached, there are multiple types of reading values, and each of these reading values can be expressed in one or more units of measure.  A list of unique measure names is included as a table called pMeasuresList and each of the units of measure is in a table called pUnitNames. The fields from these two single column tables are included as slicers on each page.

 

 

The dynamic measure to then return the value of the selected measure name/unit of measure is:

 

 

Average Measure Value =
VAR MEASURENAME = IF(HASONEVALUE(pMeasuresList[Measure Names]),VALUES(pMeasuresList[Measure Names]),"Tidal Level")
VAR UNITNAME = IF(HASONEVALUE(pUnitNames[unitName]),VALUES(pUnitNames[unitName]),"mAOD")
RETURN
CALCULATE(AVERAGE(Readings[value]),'Measures 1'[qualifier]=MEASURENAME,'Measures 1'[unitName]=UNITNAME)

 

 

Note that this has a default value - so if the user has not made any slicer selections then it defaults to the reading "Tidal level" shown in Unit Of Measure 'mAOD'

 

The same principle can be applied to the title, so that the report page title reflects what is being shown in the graphs:

Title Station Analysis =

 

VAR MEASURENAME = IF(HASONEVALUE(pMeasuresList[Measure Names]),VALUES(pMeasuresList[Measure Names]),"Tidal Level")
VAR UNITNAME = IF(HASONEVALUE(pUnitNames[unitName]),VALUES(pUnitNames[unitName]),"mAOD")
RETURN
MEASURENAME & " (" & UNITNAME & ") Station Analysis"

 

Attachment
Frequent Visitor
Posts: 2
Registered: ‎04-20-2018

Re: Dynamic Measures/Titles (Using Parameter Table)

Hi,

could ypu please tell me from where should i get current year example rain data and Historic flood warnings data?

 

Thanks,

Kailas S.

Advisor
Posts: 24
Registered: ‎06-17-2017

Re: Dynamic Measures/Titles (Using Parameter Table)

Hi

 

The Environment Agency data I used is available at http://environment.data.gov.uk/flood-monitoring/archive - this contains data both as CSVs and an API call

 

Thanks

 

Rishi

 

 

Member
Posts: 67
Registered: ‎01-31-2018

Re: Dynamic Measures/Titles (Using Parameter Table)

Hi Rishi Sir,

 

I have only one words for you.. That is "Superb". No words after that. This is the real example of playing with visualization and DAX. I just started my Data Analyst career. Working in Power Bi since 2 months. This .pbix file is my inspiration and dream, that one day, i will be able to make this kind of visualization. I have learnt Power Bi from youtube, google and some blogs.So, your .pbix file will help me to learn more. Thanks a lot sir, for sharing this awesome creation with us. It will be a new milestone for newcommer like me.I have one question for you, hope you dont mind to answer this, how much experiences you have taken to achieve this level?

Thank one again sir and if you have any tips and advice for newbie like me, then please share.

 

Sincerely,

Sayam