Returning a list of distinct rows that have been deselected by a slicer/filter
Hi All, I have a tooltip on all pages in my report that shows what the user has selected/not selected on slicers on the landing page. It works perfectly outside of the not selected "list", which currently concatenates all the "not selected" rows in the table, rather than just the distinct "not selected" rows in the table. Part of the dax is below Selection RAG = VAR SelectedValues = VALUES ( 'Account'[Account Group] ) VAR ConcatTable = CONCATENATEX ( CALCULATETABLE ( SelectedValues ), 'Account'[Account Group], ", " ) VAR ConcatTableExcept = CONCATENATEX ( CALCULATETABLE ( Account, Except(All(Account[Account Group]), SelectedValues)), 'Account'[Account Group], ", " ) The VAR ConcatTable works fine and concatenates values that have been selected in the slicer and is used when the number of selected values in the slicer is a low number The VAR ConcatTableExcept is used when the number of deselected values in the slicer is low and number of selected values is high, except the problem is it returns all the rows in the table that havent been selected in the slicer rather than the distinct values that have not been selected. I have tried to wrap the All inside of a distinct function like this Distinct(All(Account[Account Group])) and various other means but have not cracked the code yet. Some example screenshots below. When Sales Development is not selected (All rows containing Sales Development) When only Sales Development is selected (correct) Any help would be greatly appeciatedGetting info Outside Slicers & mix with Sliced data
I am attempting to get information ouside slicers using CALCULATE and CALCULATETABLE but I am unable to get anything working as I am assuming I just dont fully understand the proper implimentations. Everything I can find is constantly doing SUMs of last year finances lol Basically I have a Service table that all my visuals are based off of that has a slicer filtering the date as well as another filtering the client. So heres an example of one of my calculations: Complete Count LY = CALCULATE(COUNTROWS(cyb_service),cyb_service[_Complete/Incomplete]="Complete",'Date'[Year]=YEAR(TODAY())-1)+0 So this returns information only if the visual is not effected by a slicer. I would like to have this in a visual that is effected by a slicer, like 2 bar graphs so I can compare values. I am assuming I need something like ALL or one of those things. But I am also wondering how I make a measure and/or table that gives me information on like the Type of Service as I have a Type option ont he table and for my usual visuals I jsut drop the Type in and it seperates and counts each type. I am wondering how I go about doing that information unaffected by the slicers and into a visual with data that is effected by slicers. Preferably without having to do a special filter like the above cyb_service[_Complete/Incomplete]="Complete" as I think I would have to do one for every type. Hopefully I have explained myself well enough, I can provide any additional info if needed, but unfortunately I am unable to share the file.
DAX for selecting all of the columns and data within a table
Hi all, I am hoping someone can help? I have a bit of a DAX problem (hey who doesn’t)… I have quite a complicated Power Automate Flow that takes the output from a PBI query, saves it into sharepoint by splitting and joining multiple files, which allows me to automate an extract of 100,000+ rows into CSV. My flow works fine, except for the DAX within the ‘Run a query against a dataset’ flow. All I want to do is export the contents of the table ‘Help’. The DAX in my examples work on columns that contain numbers, but it appears to fail if I add a column that contains a String like I have in 'Help’ [Test4], 'Help’ [Test5] 'Help’ [Test6]. If i add 'Help’ [Test3] (which is numeric) to the code below it will work. I would like to use DAX to select all the columns in my table (about 35+) // DAX Query DEFINE VAR __DS0FilterTable = // Removes repeat headers FILTER(KEEPFILTERS(VALUES('Help’[Index])), and('Help’ [Index] >= @{variables('MinRows')}, 'Help’ [Index] <= @{variables('IncrRows')})) // Export content of table VAR __DS0Core = CALCULATETABLE( SUMMARIZECOLUMNS( 'Help’[File.Name], 'Help’ [Index], 'Help’ [Test1], 'Help’ [Test2]), __DS0FilterTable ) EVALUATE __DS0Core ORDER BY ‘Help’[Index] Eternally grateful for any help!Subtotal in a visual table returning zero on a measure column
Hi, I have created a visual table and I would like to get the total sum from a column that I inserted as a measure, The table keeps returning me 0, but it is expected to make a sum of each row from this visual table, I can get my result by exporting this to excel this table and then making the sum manually, but I would like to get this result automatically, this result could be a simple measure to insert in a visual card, for example, (This number is just an example, I couldn't get the right formula to it, and that's where I need help) I need help to write a measure to get this result and insert in a visual card, I know that is impossible to get my expected result in the visual table column subtotal, Let me explain the composition of this data here by using examples: (This is a fixed key table) Table1 CodeID 800717 800275 800284 800277 800288 800283 800909 800272 800473 (This is a dimension table, it might have some IDs from Table1) Table2 CodeID Value 800717 224,2 800275 281,1 800284 351,3 800277 204,1 (This is a dimension table, it might have some IDs from Table1) Table3 CodeID Value 800717 287,1 800275 297,2 800288 223,2 800283 245,3 800909 256,7 800272 257,5 800473 210,2 AVERAGE 253,9 (Then I create this visual table, by using the Table1 as structure, and values from table 2 and 3, plus the measures) =Data from Table1 =IF( ISBLANK([ValuesTable3]), ValuesTable2, ValuesTable3) =[Measure1] - CALCULATE( AVERAGE(Value'Table3'), ALLSELECTED('Table3')) = [FixedValue] * [Measure2] CodeID ValuesTable2 ValuesTable3 Measure1 Measure2 FixedValue Measure3 800717 224,2 287,1 287,1 33,21428571 2469,2 82012,71 800275 281,1 297,2 297,2 43,31428571 4697,2 203455,86 800284 351,3 351,3 97,41428571 3001,1 292350,01 800277 204,1 204,1 -49,78571429 2002,4 -99690,91 800288 223,2 223,2 -30,68571429 6447,2 -197836,94 800283 245,3 245,3 -8,585714286 946,9 -8129,81 800909 256,7 256,7 2,814285714 876,3 2466,16 800272 257,5 257,5 3,614285714 1621,7 5861,29 800473 210,2 210,2 -43,68571429 784,6 -34275,81 SUBTOTAL 253,89 253,89 0 22846,6 0 (Expected result here should be 246212,56) Thank you for your support, community,
Optimize DAX Code
Hi, I have created this DAX command: ∑ plná erózia kmeň = //: Výpočet období pre time intelligence VAR EndDate = MAX(tbl_kalendar[datum]) VAR StartDate = EDATE(EndDate,-12)+1 VAR ActualYear = DATESBETWEEN(tbl_kalendar[datum],StartDate,EndDate) VAR EndDateLY = EDATE(MAX(tbl_kalendar[datum]),-12)+1 VAR StartDateLY = EDATE(MAX(tbl_kalendar[datum]),-24)+1 VAR Previous_Year = DATESBETWEEN(tbl_kalendar[datum],StartDateLY,EndDateLY) VAR Date_period = DATESBETWEEN(tbl_kalendar[datum],StartDateLY,EndDate) //: Výpočet pomocnej tabuľky, ktorá je rovnaká, ako v prípade full_erozia_kmen VAR Result1 = GROUPBY( CALCULATETABLE( SELECTCOLUMNS(Data, "id_agent",data[id_agent], "id_klient",data[id_klient], "skupina_produkt",data[skupina_produkt], "dist_ext_cislo_PY",CALCULATE( DISTINCTCOUNTNOBLANK(data[ext_cislo]),ALLEXCEPT(data,data[id_agent],data[id_klient],data[skupina_produkt]), TREATAS(Previous_Year,tbl_kalendar[datum]), KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0)), "sum_prov_PY",CALCULATE(VALUES(data[prov]), TREATAS(Previous_Year,tbl_kalendar[datum]), KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0)), "dist_ext_cislo_AY",CALCULATE( DISTINCTCOUNTNOBLANK(data[ext_cislo]),ALLEXCEPT(data,data[id_agent],data[id_klient],data[skupina_produkt]), TREATAS(ActualYear,tbl_kalendar[datum]), KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0)), "sum_prov_AY",CALCULATE(VALUES(data[prov]), TREATAS(ActualYear,tbl_kalendar[datum]), KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0)), "rozdiel", CALCULATE(VALUES(data[prov]), TREATAS(Previous_Year,tbl_kalendar[datum]), KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0)) - CALCULATE(VALUES(data[prov]), TREATAS(ActualYear,tbl_kalendar[datum]), KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0))), TREATAS(Date_period,tbl_kalendar[datum]), KEEPFILTERS(data[id_klient]<>BLANK() && data[id_druh]=2 && data[prov]>0)), [id_agent],[id_klient],[skupina_produkt],[dist_ext_cislo_PY],[dist_ext_cislo_AY],"sum_prov_PY",SUMX(CURRENTGROUP(),[sum_prov_PY]),"sum_prov_AY",SUMX(CURRENTGROUP(),[sum_prov_AY]),"rozdiel",SUMX(CURRENTGROUP(),[rozdiel])) //: Do pomocnej tabuľky pridáme dva výpočtové stĺpce, rozdiel medzi dist_ext_cislo PY a AY, prepočítaný stĺpec rozdiel, aby v prípade záporných hodnôt bola zobrazená 0 (resp. Blank) VAR Result2 = ADDCOLUMNS(Result1,"rozdiel_dist_ext_cislo",[dist_ext_cislo_AY]-[dist_ext_cislo_PY]) //: Suma provízií zo zmlúv, ktoré existujú v predošlých 12M (PY) a nemajú žiadny záznam v posledných 12M (AY) VAR Result = SUMX(FILTER(Result2,[dist_ext_cislo_PY]<>BLANK() && [dist_ext_cislo_AY]=BLANK()),[sum_prov_PY]) return Result When I use this DAX command in card visualization it works perfectly, hovewer, When I use this metric in line chart with time dimension then I have this message: When I filter specific agent the chart appears. Is there any possibility to optimize this code, so I can use this command for the whole company without filtering specific id of agent? Thanks for help. Lucia
