Visualization Not working as needed with dax measure
Visualization drill down not showing. I can only drill through the non-compliant data and not the compliant ones. I have created a formula to check how much data is compliant and non-compliant. For example, if the customer has all the fields populated then he is compliant, so their details are missing from the Query1 table. If the member has a missing information, then he is non-compliant and will be available in the query table. Compliant Formula = DISTINCTCOUNT('DW v_Member'[MemberNum])/CALCULATE(DISTINCTCOUNT(Query1[MemberNum]),ALL(Query1)) Non-Compliant= 1- ( DISTINCTCOUNT('DW v_Member'[MemberNum])/CALCULATE(DISTINCTCOUNT(Query1[MemberNum]),ALL(Query1))) I have also created a calculated column in the member table to check if I can showcase both compliance and Non-compliance data but that is also not working Compliance Check = IF( COUNTROWS( FILTER( Query1, Query1[MemberNum] = 'DW v_Member'[MemberNum] ) ) > 0, "Non Compliant", "Compliant" ) Table information : Dw_Member: is a fact table with all customer information Branch is a fact table with all of company branches Date table Query 1 comprises of only those customers whose profile has a missing information for example date of birth or address etc. A customer can appear more than once if they have multiple information missing Donut chart appears correctly with compliant and non-compliant measures but all other measures are not getting drilled through see second chart
Calculating cumulative values with drillable dates
Hello 🙂 I would like to visualize transaction data in a bar and line chart. I want to show the cumulative total on the line and the transaction by period in the bars. I would like to be able to drill up and down. Model The relevant sections of my model are set up like this: Measures / DAX Used Total Completed Transactions = COUNT( Transactions[Transaction ID] ) Total Transactions = CALCULATE( [Total Completed Transactions], USERELATIONSHIP( TRANS_DateTable[TRANS_Dates], TRANS_Events[Initial Transaction Date] )) Total Successful Transactions = ([Total Transactions] - 'Totals Measures'[Total Cancelled] - 'Totals Measures'[Total Returned] - 'Totals Measures'[Total Defective]) Cumulative Total Transactions = CALCULATE('Totals Measures'[Total Transactions], USERELATIONSHIP( TRANS_DateTable[TRANS_Dates],TRANS_Events[Initial Transaction Date] ), TRANS_Events[Initial Transaction Date] <= MAX(TRANS_Events[Initial Transaction Date])) I have tried two methods to get my desired results. I should also note that my cumulative measure did use the FILTER and ALLSELECTED formulas at one point, but there was no difference in the results, so I removed it). Method 1: Fields: Shared Axis: TRANS_Dates Column Values: Total Transactions Line values: Cumulative Total Transactions Chart results: In table format: Issue with Method 1: The cumulative value does not calculate/display correctly. Method 2: Fields: Shared Axis: Initial Transaction Date Column Values: Total Transactions Line values: Cumulative Total Transactions Chart Results: In table format: Issue with Method 2: Cannot drill up or down. Desired results: A table and bar and line chart that displays totals for distinct periods as well as the cumulative total for that period. I also want to be able to slice by TRANS_Date and drill up and down for a range of viewpoints, like below (with contrived data): Year over Year perspective: Month over Month perspective: Week over week (end of week) perspective: Any help you could offer would be greatly appreciated! Thank you!
