'dax'

9 Topics

Top10 + Others with duplicate values
Hi, In the past I was able to create a calculated column that made Top10+Others working Sales Top10+Others = VAR RankProductbySales = RANKX(All('Product'), [Sum Sales Quantity] ,,DESC) RETURN IF (RankProductbySales<=10,'Product'[productname], "Others") That worked. But it worked because productname was unique in the table. I recreated this in the attached pbix. Table Product_V1 connected to Sales and as you can see in the visual with column 'Product_V1' it gives the right result. But I created a second version, table Product_V1. This reflects my current challenge, wehereby this Product_v2 is just very simplified. The issue is that I now want to Rank by owner. But an owner can have multiple products. In other words, the owner is not unique in the product table. How do I do this? If you look at the left visual with column 'owner' it is rnked on product. But I want it to be ranked as shown in the left visual with column 'owner'. Whereby it contains three lines: Rob with 1125, John with 700 and Others with 1225. https://www.dropbox.com/scl/fi/acmlmt4cvgmrd9ckj1en8/TopN-Others_2.pbix?rlkey=n5unb6h8hfq6uh8y1ulnbekp3&st=qrbchmbt&dl=0 https://www.dropbox.com/scl/fi/05u3t9mwp73l9op2ys7gi/TopN-Others_2.xlsx?rlkey=04cjme3h457abgyjfpw2omy5q&st=zzlz25so&dl=0 Hope someone can help. lbendlin bhanu_gautam Regards Ron
Solved
PowerRon
1 year ago Place DAX Commands and Tips
684Views
0likes
2Comments
Card visual is showing blank but matrix is showing value in power bi
Hi i have a card visual and a matrix visual, along with that i have a date slicer (between). scenario: when the date slicer changes the value in the card and well as matrix gets adjusted to the last date in the date slicer. matrix has categories(category column) as a row. Card visual is showing the over all values without the category. problem: for few dates, there are few category missing. When i slelect those dates , card visual does blank, but matrix visual shows value for which the category is present for that day and blank for the category which is not present for the day, shown below. Card visual should show values of 3 of the above category but it just showing blank. Below is the calculation measure for card visual: please give me a solution
Mrignalini
1 year ago Place DAX Commands and Tips
661Views
0likes
3Comments
SELECTEDVALUE inside table constructor
Hi, I have the following measure inside a filter context where the only selected value for column 'ParameterTable'[ItmNam] is "A" (in fact it's a parameter table): CALCULATE( COUNTROWS('Table'), 'Table'[ItemName] IN {SELECTEDVALUE('ParameterTable'[ItmNam])} ) But the measure is not counting the rows in table 'Table' where ItemName = "A". If I change the measure as follows, it works fine: CALCULATE( COUNTROWS('Table'), 'Table'[ItemName] IN {"A"} ) Why SELECTEDVALUE doesn't works inside a table constreuctor? Where is this limitation written or explained? Note: I don't need a workaround (variables can solve it), I need the conseptual explanation or documentation plese. Thank you! marcorusso
Solved
juan_pablo
1 year ago Place DAX Commands and Tips
1.8KViews
0likes
5Comments
How to show max value from slicer in pivot
Hi, I have a pivot with documents and a slicer on the document date. The pivot contains the document and the respective document date. My problem: The pivot should be extended by another column - with the maximum selected document date of the slicer. The result is a cross product. What am I doing wrong? Thank you for your help. MyMeasure: MaxSliceDocumentDate = maxx(allselected(TimeDoc), TimeDoc[Date Date]) Starting Point: Result after adding Measure: Needed Result:
Solved
GeorgWildmoser
2 years ago Place DAX Commands and Tips
1KViews
0likes
3Comments
DAX calculate Previous month lost customer revenue
Hi guys, I am having trouble calculating the previous month lost customer revenue. My model relies on a single way relationship between thr fact table and the time table, so that is not possible to change. I would like to display the measure "Churn_lost_$" as shown in the picture below. Thank you very much! 🤙 You can donload the model here: https://drive.google.com/file/d/1MKFmtBIm1Z-yJ0--FhVRuIrZqWVV166c/view?usp=sharing
APIR
2 years ago Place DAX Commands and Tips
1.6KViews
0likes
9Comments
Visual calculations only uses fields in the visual?
Hi - I have been testing the new "Visual Calculations" feature for use cases where I need to apply filter conditions on individual measures inside a table. However, it seems to be limited to only fields/columns present in the visual itself and cannot use other fields in the model. For example, I need to produce a table with the measure [Revenue] and then immediately next to it display [Revenue] filtered for just one department (the unfiltered [Revenue] measure sums revenue from ALL departments). There is no other way to generate this result without creating a second custom measure in the model for [Revenue - Specific Department]; I was hoping to accomplish this with a single measure using visual calculations and applying the filter condition for the one department on the [Revenue] measure as the second column in a visual calculation. But this does not seem possible because the [Department] field needs to actually be incorporated into the visual for it to be used; this results in a change to the table itself slicing by [Department] which was not the result I need. Is there any way in visual calculations to use columns not present in the visual? And if not, is that on the roadmap?
Solved
mbbozzuto
2 years ago Place DAX Commands and Tips
2.2KViews
0likes
4Comments
Don't show data before user selects from slicer
In a report, I need to hide the data in a table visual until users selects a value from the slicers. They NEVER need/want to see all the data. I have seen it done and it looks so easy! The report only has a single data souce called EBI_USER_DETAIL_FINANCE_SAP_EXPORT_V and the column in the slicer is called SERVDATE 1 - I created a measure on the table with this formula: Filtered = int(ISFILTERED(EBI_USER_DETAIL_FINANCE_SAP_EXPORT_V[SERVDATE])) 2 - I added the measure as a filter to the table visual - I placed the measure on a card so that I can see it's value and it showing correctly - 0 when nothing is selected and 1 when a value is selected. This is the card when no selection is made: I would expect that the table would hide the data with this value but the table data is visible A couple of hints - 1 - It all works when I hard code the values in the measure instead of using the IsFiltered DAX code. This code hides the data on the table exactly as I need it to: Filtered = 0 This shows the data: Filtered = 1 While this does not do what is needed: Filtered = int(ISFILTERED(EBI_USER_DETAIL_FINANCE_SAP_EXPORT_V[SERVDATE])) 2 - I placed the measure on the table so that I can see the vaule from that perspective and it is never 0 - it always is 1. I created table with a new measure with this formula: Filtered on diff table = int(ISFILTERED(EBI_USER_DETAIL_FINANCE_SAP_EXPORT_V[SERVDATE])) I used a card to see both measure values - Value in slicer not selected: Value selected: A table visual of this new table shows the correct value - Nothing selected: Added this to the filter of the main table visual: The data still shows when the value of the new measure is 0! Any help would be GREATLY appreciated! Marc Cahn
Solved
Anonymous
2 years ago Place DAX Commands and Tips
1.5KViews
0likes
7Comments
Weekly year over year cumulative totals when weeks start on different dates
Hi all I have a table for ticket purchases that looks like this... Ticket sales for 2023 start on a different date than in 2024. In order to do a comparison, I've added week numbers for each year starting at the first date tickets were sold (formatted as dd/mm/yyyy). I need to be able to plot cumulative totals on a graph like below but haven't been successul in making it work. Could someone please help me create a measure that will do this? Thanks
Solved
onedayover
2 years ago Place DAX Commands and Tips
811Views
0likes
2Comments
Daily report counter isn't working
Hello, I'm facing an issue where the counter is always at 0 when i'm looking at the daily report. for example this is the measure: # Weld_reports_cur_day = VAR x = CALCULATE(DISTINCTCOUNT(qryAssemblyProcessReport[OrderID]), NIMI_CALENDAR[# IsCurrentDay] = 1, tblStations[StationCode] = 30) return IF(ISBLANK(x), 0, x) as for the measure within the measure above, this is the code: # IsCurrentDay = IF(NIMI_CALENDAR[date] = TODAY(), 1, 0) In the tables everything is there, no information is missing. What could be the issue?
Solved
545
2 years ago Place DAX Commands and Tips
603Views
0likes
1Comment