User Profile
Jayshamone
Helper I
Joined 5 years ago
User Widgets
Contributions
DAX - Count number of Transactions with n Items
Hi, I have a detailed data model and I would like to calculate how many transactions there are with n items per transaction. In SQL this would look something like this: select numOfItems, count(*) as transactions from ( select transaction_id, count (*) as numOfItems from trans_articles group by transaction_id ) temp group by numOfItems However I have no idea how a DAX-measure could look like that generates a result like this. I've added a .pbix file with some demo data as well as a python-visualisation of the expected / desired result. You can download it here. Could someone help, please? Thanks in advance JustusSolved2KViews0likes3CommentsRe: DAX How many datasets have changed on an ordinal scale?
A snapshot would be enough. Although the number of rises / falls would be even better. We only have 1 value per user per month - which is always assigned to the first of the month. So, something like Previousmonth() / dateadd([Date], -1, month) would in principle work - however, I could not get the whole formula to work.600Views0likes0CommentsDAX How many datasets have changed on an ordinal scale?
Hi, I have a dataset with the following tables: [Users], [Date], [Tier], [Facts]. The [Fact Table] includes the combination of the three other tables. The values [Tier]-Table are on an ordinal scale. The question, I would like to answer is: How many users have changed from a lower tier to a higher tier (and vice versa) within one month. In an example: Facts [Date, User, Tier] = {[2021-01-01, 1, 1], [2021-02-01, 1, 2], ....} Here User 1 has sunken from tier1 to tier2 in the timespan from 2021-01-01 to 2021-02-01. How can I define that measure to get the information how many users have sunken and how many have risen? I included a demo report / dataset. Thank you.704Views0likes2CommentsRe: AVERAGE If not every dataset exists
ryan_mayu Thanks for the file. However the server needs to store all of the Information. We store 1 dataset per article (roughly 30.000) for each store (35) per day (6 months). In order to even have a dataset for articles that haven't changed stock for a long time, we also store the last value that was longer than 6 months ago. This means we would have to store an additional minimum of 189 Million - in reality more than a billion datasets on the server. Would it be possible to just do the calculation using DAX?784Views0likes1CommentAVERAGE If not every dataset exists
Hello, I would like to calculate an average value for our product stock. However we do not have a value for each day, but only for each day with a change. It would be possible, but highly memory-intensive to precalculate all non-existent values. Is there a possibility to add the last value using DAX syntax. The table below shows how it should be calculated. Cum_sum = 66 Avg := 66 / 7 = 9,43 Date Value Cum_Sum 01.06. 7 7 02.06. 8 15 03.06 23 04.06. 9 32 05.06. 13 45 06.06. 58 07.06. 8 66 Thank you for your ideas. JustusSolved831Views0likes3CommentsHide Visual Based On Measure / Attribute
Hi, is it possible to completely hide a visual based on a measure / attribute? Use Case: We have several tiers that decide which pages a user can see. I would like an overlay that tells lower-tier-customers to book a higher one. I am familiar with the option to set all colours as transparent. However, if I put the message on the top layer and hide it with transparent background / message, the user cannot interact with the visuals below. Setting up all other visuals with transparent colours is highly labour-intensive. So I wish there was an option to dynamically use the selection pane > hide feature based on a constant measure (either 1, 2 or 3). Using bookmarks is not really an option due to usability. Thanks Justus688Views0likes1CommentRe: Basket Analysis
I was able to include the filters like this: Sum Value Basket = CALCULATE( sum('Fact Positions'[Value]), USERELATIONSHIP('Dim Basket'[Article Index], 'Fact Positions'[Article Index]), REMOVEFILTERS('Dim Articles'), CALCULATETABLE(VALUES('Fact Positions'[Transaction ID])), EXCEPT(VALUES('Dim Basket'[Article Index]), VALUES('Dim Articles'[Article Index])) )3.4KViews2likes1CommentRe: Basket Analysis
Hey, the result seems to be exactly what I'm hoping for. Thank you. However I need to be able to write the DAX so that the users can use the measures without explicitly filtering the top 1 ... I'm trying to implement this approach into our model and will accept your solution as soon as it works 🙂3.4KViews0likes0Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.