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 All,
I am new to PowerBi and working on a DAX formula to calculate Mode of my data set. (see attached)
I got following formula from one of the sites, which works correctly except for few columns. I have mentioned the same in the sheet tab. Can someone help pinpoint why Excel Mode Formula and PowerBi Mode DAX is not matching for these columns whereas it matches for rest of the columns?
mode2 = VAR myTable = SUMMARIZE(Data,Data[Value],"Count",COUNT(Data[Value])) VAR myTable1 = FILTER(myTable,[Count]=MAXX(myTable,[Count])) VAR mode1 = MAXX(LASTNONBLANK(myTable1 ,[Value]),[Value]) RETURN mode1
Solved! Go to Solution.
Found the problem. It was not an error, but because that column had 2 modes, DAX was picking up the max among the two. Excel by default picks up minimum of the two. I just updated the formula as Maxx(firstnonblank(mytable1,[value]),[value]) and it now gives same result as excel file.
Found the problem. It was not an error, but because that column had 2 modes, DAX was picking up the max among the two. Excel by default picks up minimum of the two. I just updated the formula as Maxx(firstnonblank(mytable1,[value]),[value]) and it now gives same result as excel file.
Hi @Anonymous
Could you share your sample data which could reproduce your scenario and your desired output?You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.
Regards,
Unfortunately I cant upload the PBIX because it has lot of sensitive data. I had uploaded the sample excel data sheet in the following link. Have a look and use the DAX i had posted before to check the error.
Thanks,
AK
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |