Greeting
Create a personalized greeting for your users with this Quick Measure. Uses USERNAME function so that doesn't work so well with Publish To Web. Greeting = VAR user = USERNAME() VAR hour = HOUR(NOW()) VAR minute = MINUTE(NOW()) VAR prefix = SWITCH( TRUE(), hour<12,"Good morning ", hour<17,"Good afternoon ", "Good evening " ) RETURN CONCATENATE(prefix,user) Place this measure in a Card visualization. If you have a lookup table of user names and friendly display names, you can do this: Greeting1 = VAR user = USERNAME() VAR display = LOOKUPVALUE(Table1[User],Table1[Email],user) VAR display1 = IF(ISBLANK(display),"Player One", display) VAR hour = HOUR(NOW()) VAR minute = MINUTE(NOW()) VAR prefix = SWITCH( TRUE(), hour<12,"Good morning ", hour>=12 && hour<17,"Good afternoon ", "Good evening " ) RETURN CONCATENATE(prefix,display1) eyJrIjoiMjNiMmYxMWQtMDc2OC00ZGY0LWE4NDctZWFlMWU3MjExNzQ4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9QUARTILE.EXC
In my recent quest to create or catalog as many DAX equivalents for Excel functions, this just adds to the struggles with Excel's QUARTILE function. So, in numerous locations, such as this; not that...God forbid, Quora is authoritative about anything, but in mulitiple places there are two documented methods of calculating 1st and 3rd quartiles and they all seem to essentially agree about the general nature of those two methods. So I implemented the exclusive method here but the results seem to come out to more along the lines of the inclusive method, not that I trust Excel's calculations of those either. So, who knows at this point. Here it is though. QUARTILE.EXC = VAR __Values = SELECTCOLUMNS('Table',"Values",[Column1]) VAR __Quart = MAX('Quartiles'[Quart]) VAR __Median = MEDIANX(__Values,[Values]) VAR __Count = COUNTROWS(__Values) VAR __Quartile = SWITCH(__Quart, 0,MINX(__Values,[Values]), 2,__Median, 4,MAXX(__Values,[Values]), 1, VAR __Median = IF( ISEVEN(__Count), MEDIANX(FILTER(__Values,[Values] < __Median),[Values]), MEDIANX(FILTER(__Values,[Values] <= __Median),[Values]) ) RETURN __Median, 3, VAR __Median = IF( ISEVEN(__Count), MEDIANX(FILTER(__Values,[Values] > __Median),[Values]), MEDIANX(FILTER(__Values,[Values] >= __Median),[Values]) ) RETURN __Median ) RETURN __Quartile All I can say is that apparently either everyone else in the entire world (as far as I can find) is wrong about how to calculate quartiles or...maybe I am missing something. Something else that bugs me, all of the documentation on QUARTILE.INC, QUARTILE.EXC, PERCENTILE.INC, PERCENTILE.EXC all focus on the "inclusive/exclusive" part about the kth values from 0..1. Except that seems like the least important part to me because there are clearly different methods going on here in terms of how these functions compute the quartiles/percentiles because you can get very different answers, especially when dealing with even numbers of items. The fact that you can't use 0 and 1 in one of them seems like the last thing that you would want to explain but rather explain why the calculated values are different? And another thing with regard to the "interpolation", apparently that is why the numbers generated for the 1st and 3rd quartiles in Excel varies from the way everybody else does it so how exactly is this interpolation happening and why is it better or worse than the way everyone else seems to do it? eyJrIjoiNzQxZTc1ZDgtMmE2Ni00NDE0LWExNjktZWJiMzBhZTk3Y2UyIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
DIY SVG Icon Bar For Table or Matrix
How to use? link https://app.powerbi.com/view?r=eyJrIjoiZDE4NmYwMTQtMzFiZi00MGM0LTgyODAtODk3MTkxZTBiODU2IiwidCI6IjI5Y2JkNTY4LTBlOWItNDQ0Zi1iZTA1LTYxNjMyOTAzNjJmZSJ9 eyJrIjoiZDE4NmYwMTQtMzFiZi00MGM0LTgyODAtODk3MTkxZTBiODU2IiwidCI6IjI5Y2JkNTY4LTBlOWItNDQ0Zi1iZTA1LTYxNjMyOTAzNjJmZSJ9DIY SVG Waffle Charts
Search & Choose an icon, then copy the SVG Measure below. https://app.powerbi.com/view?r=eyJrIjoiM2EwYjEwNDAtZTAyNS00NGFmLWE3YmUtNWYwNTBmMWFhNTY3IiwidCI6IjI5Y2JkNTY4LTBlOWItNDQ0Zi1iZTA1LTYxNjMyOTAzNjJmZSJ9 eyJrIjoiM2EwYjEwNDAtZTAyNS00NGFmLWE3YmUtNWYwNTBmMWFhNTY3IiwidCI6IjI5Y2JkNTY4LTBlOWItNDQ0Zi1iZTA1LTYxNjMyOTAzNjJmZSJ9Map Lines
Came out of this conversation: Create Origin To Destination Map In BI Desktop - Microsoft Fabric Community. Here's a way to create origin to destination lines on a map given a table of cities. CityLatitudeLongitude Columbus, OH 39.9611111 -82.9988889 London, UK 51.5 -0.116667 Kansas City 39.099912 -94.581213 St. Louis 38.627089 -90.200203 From and To = VAR __NumSteps = 100 VAR __Steps = SELECTCOLUMNS( GENERATESERIES( 1, __NumSteps, 1 ), "Step", [Value] ) VAR __BaseTable = FILTER( GENERATE( SELECTCOLUMNS( 'Cities', "From City", [City], "From Long", [Longitude], "From Lat", [Latitude] ), SELECTCOLUMNS( 'Cities', "To City", [City], "To Long", [Longitude], "To Lat", [Latitude] ) ), [From City] <> [To City] ) VAR __String = CONCATENATEX( __BaseTable, [From City] & "^" & [From Long] & "^" & [From Lat] & "^" & [To City] & "^" & [To Long] & "^" & [To Lat], "|" ) VAR __Count = COUNTROWS( __BaseTable ) VAR __Table = ADDCOLUMNS( GENERATESERIES( 1, __Count ), "__Data", SUBSTITUTE( PATHITEM( __String, [Value] ), "^", "|" ) ) VAR __Result = FILTER( SELECTCOLUMNS( ADDCOLUMNS( GENERATE( __Table, __Steps ), "Latitude", PATHITEM( [__Data], 3 ) + ( [Step] / __NumSteps ) * ( PATHITEM( [__Data], 6 ) - PATHITEM( [__Data], 3 ) ), "Longitude", PATHITEM( [__Data], 2 ) + ( [Step] / __NumSteps) * ( PATHITEM( [__Data], 5 ) - PATHITEM( [__Data], 2 ) ) ), "Latitude", [Latitude], "Longitude", [Longitude], "From", PATHITEM( [__Data], 1 ), "To", PATHITEM( [__Data], 4 ) ), [From] <> [To] ) RETURN __Result eyJrIjoiMDZkNjhlMmItZTg1ZC00ZmM2LTkwZTEtNDJiZDA3ZGFlNDRjIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9MAGIC!
This is my 150th Quick Measure! I wanted to make it extra special. You can read all about the creation of it here. The short form is that this combines an advanced machine learning model that uses the entire history of the Power BI forums to find solutions to questions posed within a modified Q&A visualization coupled with a custom visualization and advanced DAX to return the solution returned by the machine learning model. Because this solution includes new Power BI features that are not yet available generally, Microsoft has requested that I do not post the PBIX file. And it wouldn't work anyway with any current publicly available version of Power BI Desktop anyway. Try it out!! eyJrIjoiZDhjYjBhMWItM2YwYS00NzliLWFiNmMtZThiOWQxM2M1MDg1IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Cthulhu
Why Cthulhu? Because the mental gymnastics required to figure this out nearly drove me insane. And because the exact reason you would need this measure is probably beyond the comprehension of mere mortals. Besides, what else am I supposed to call it, "Repeating Counter Indexing Thingy"? But, if you need a column or measure that counts a group of things consecutively but restarts after a non-consecutive row, well then you are likely the second person to need this... Cthulhu = VAR __index = CALCULATE(MAX([Index])) //What is my current row index? VAR __group = CALCULATE(MAX([Animal])) //What is my current group? VAR __tmpTable1 = FILTER(ALL('Cthulhu'),[Animal]=__group&&[Index]<__index) //Return all rows earlier than the current row within the same "group" VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Cthulhu'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index])) //For each returned row, calculate the difference between the current index value and the previous index value within the same group. For rows in grouped sequence, this will be 1 but for rows within a group that are out-of-sequence this value will be greater than 1 VAR __max = MAXX(__tmpTable2,[Index]) //Figure out the max index in the current filtered table. VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index]) //In order to account for "skips" in the grouping, figure out the max index value of the latest "skip" (the row right after the skip where the group starts again) This will be the greatest index where the difference from the previous index in the same group is greater than 1 (previous row) VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) //Filter out all the other junk because we don't want to count rows before the skip RETURN IF(ISBLANK(__max),1,IF(__max=__index-1,COUNTROWS(__tmpTable3)+1,1)) //If __max is blank, we know that we are at the start of the table, so 1. If the max index of our original table is 1 less than the current index, we know that we are in sequence so we count all of our filtered rows (which don't include rows past a "skip"), otherwise return 1 because we know we are on the row immediately after a "skip. The other person would be this guy Anonymous in this thread: https://community.powerbi.com/t5/Desktop/Consecutive-Row-Counter-Column/td-p/509553/highlight/false eyJrIjoiMTBhYmFlZjMtZDhhMy00MGFjLThkZWQtNDc5MDM2M2ZjN2UzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
