DAX Commands

[ankitpatira]

I want to submit below DAX commands as my all time favourite along with sample pbix file and how I've used them numerous times in my reports and dashboards and have worked without glitch.

HASONEVALUE - This is useful DAX function that returns boolean value (true or false) based on the selection made in current context. So if I've used say for example STATE column as Axis for my bar chart and I select one particular bar out of all bars in the bar chart then HASONEVALUE will return true otherwise false. So now I have a way to find out if there is an interaction / selection made on a bar chart.

FIRSTNONBLANK - This function has several uses and would depend on the situation you're using it for. Basic syntax for function goes as FIRSTNONBLANK(Column, Expression). Think of it as a looping function that goes through each rows in column and return first value of that column where Expression is not blank.

So in this case I want to demonstrate use of both of these function together to achieve title in power bi reports that changes as user makes selection in power bi reports. As you will see in attached pbix file I am using Title measure on KPI visual as title for the report. When user interacts with either pie or column chart title changes based on selection made. 

Here using IF condition I am checking if selection is first made on pie chart. HASONEVALUE for column used in pie chart if returns true then selection is on pie chart and I constrcut title by identifying value which is selected from the column using FIRSTNONBLANK function and concatenating that with other strings and sum of value of other column.

In case HASONEVALUE returns faslse then I am checking if selection has been made on the second visual which is column chart and constructing title for selection made on that visual.

And if no selection is made ie HASONEVALUE doesn't return either true or false then simply showing static title.

Please use this link to download pbix file.

[BIghost]

Please share the PBIX file with me. It is no longer available in dropbox

[ngadiez]

Hi Ankit,

Do you have other links I can download from?

It seems like I cannot access dropbox from here.

Thank you.

[AlexNYExcel]

Very useful formula is to calculate measure for any group in Dimension Column.

Example, you have table with Customers and columns ID, Customer Name, Customer Industry, etc.

The question you might have is to group Clients by their contribution to the total of Industry.

According to Pareto rule (80/20) there will be some very large contributors and bunch of small one.

% contribution = SUM(fact_table[column])/CALCULATE(SUM(fact_table[column]),FILTER(table, table[column]=EARLIER(table[column])))*100

Contributors Groups = IF(table[% contribution]>90, "1. 90%+", 
IF(table[% contribution]>75, "2. 75%+", 
IF(table[% contribution]>60, "3. 60%+", 
IF(table[% contribution]>50, "4. 50%+", 
IF(table[% contribution]>40, "5. 40%+", "6. other")))))

It could be used for grouping anything, for example for Client you might have multiple locations and unique ID for each location, but single NAME - so you could calculate MEASURE and assign it to some sort of Group/Bracket that you could move to slicers.

Let me know if it was useful!

Alex

[Habib]

Good DAX functions. Thanks for sharing.

[Tridiv]

Hi, thanks for this but the example file link is broken

[ankitpatira]

Hi @Tridiv, just tested link and it works. Please let me know if it still doesn't work for you and i will put it on one drive or google drive.

[Tridiv]

Thanks both, looks like my internet was playing up that day. it does work fine. 

[Sandy]

Maybe the link got fixed?  It works for me.