Power BI Measure without relationship between tables
Hi, I have a problem with a measure I found in a customer's report because it works but I don't understand how. Basically we have two tables, one fact table and one dimension table, the measure performs a count of the rows of the fact table under some specific filters. The problem is it uses a column of the dimension table in order to filter, but there is no active relationship between these two tables and it is not specified inside the DAX formula. There is an inactive relationship between these two tables but it a many to many relationship with no real usage. I don't understand how the results that the measure gives are correct and actually filtered without an explicit nor implicit relationship. Thank you in advance
Need help to use inactive and active relationship simultaneously in dax
Hello, I am trying to use active and inactive relationship based on the Primary Key in a single measure to use it on a single visulaization. Scenario: If Key is found in Table C via active realtionship use active relationship in measure to find out sales. If key is not found via active realtionship then compare Table A key with Table C using inactive relationship Tried below measure but this doesnt seem to be working userrelationship = if(SELECTEDVALUE('Table B'[Key])=SELECTEDVALUE('Table c'[Key]),[salesnew], CALCULATE([salesnew],USERELATIONSHIP('Table C'[Key],'Table A'[Key]))) Thanks
SelectedValue and Inactive Relationship
Hi, I'm current using the below dax formula as a dynamic title field for a visual. Slide 12 Title = "Example Text - "&SELECTEDVALUE(Calendar_Table[Quarter Name]) &" " &SELECTEDVALUE(Calendar_Table[Year])&" - Example Text" The idea is that when the visual is filtered for a specific quarter and year then it will return the following Example Text - Quarter 3 2022 - Example Text However recently I've added an inactive relationship from a date field in a fact table to the main calendar table. Following this addition whenever my visual is filtered for the current quarter it does not return the Quarter name. But when it is filtered for any other quarter it works correctly. Note - the new date field (frm fact table) that I've connected to the calendar table does have dates that are outside of the calendar table's dates. Note 2 - When the new date field is connected I can see a blank option appears when filtering the visual (which is not there without the inactive relationship). And this blank option only appears when filtering for the current quarter. Note 3 - I can make the formula work if I add two filters (one filtered for the current quarter - the other filtering out blank values). Any Other ideas why that might be? Thanks,
Summarize with multiple inactive relations and cross filtering visuals not working.
I am trying to summarize total items using inactive relations, which i am able to do separately using "userelationship" function. I have 3 inactive relations to different dimension tables. Everything looks great when I visualize them separately. The challenge is when cross-filtering the visuals, The filter doesn't flow to other Tables as they don't have active relations. I tried using the below function which doesn't work. Even tried replacing Userelationship with "CrossFilter" which dint help. CALCULATE ( [Total items], USERELATIONSHIP ( Date[Date], DataTable[CreatedDate] ), USERELATIONSHIP ( dimAging[OpenAge_Bucket], DataTable[Open_Age_Bucket]), USERELATIONSHIP (dimType[Open_type],DataTable[Open_type]) ) This is how my relationship looks: Below is my challenge on getting cross-filtering working
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!DAX using USERELATIONSHIP is not working for an inactive relationship
Hi Folks, Table1: Dim_LaborShift Table2: Fact_HoursPlanner Data model: I am trying to create matrix which shows "Total Shift Length" for a particular User on a particular Date. As you can see from the data model, I have an INACTIVE relationship between "Name" (Dim_LaborShift) and "TEXT_VAL" (Fact_HoursPlanner) and I am using this relationship in the following DAX with USERELATIONSHIP to establish the relation but it is summing all ShiftLengths in the Dim_LaborShift table and populating those for ALL Users on ALL Dates. Basically, in the matrix I want to show only values where the User has been assigned one/more Shift(s) and the Total Length of those shift(s) for a given date. Per the example in the screenshot, it should be: Matrix: User: 11/21/20 | 11/22/20 | 11/23/20 | 11/24/20 | 11/25/20 | 11/26/20 | 11/27/20 1457178 : 15.5 (i.e 7.5 + 8.00) | | | | | | | 295949: 5 | | | | | | | DAX usage: Total Shift Length = CALCULATE(SUM('OLAP LPP_Dim_LaborShift'[ShiftLength]), USERELATIONSHIP('OLAP LPP_Dim_LaborShift'[Name], 'OLAP CUBE_LPP_Fact_HoursPlanner_WB'[TEXT_VAL])) I am not quite sure why USERELATIONSHIP isn't working here and ANY help/guidance in achieving the above result would be great. amitchandak
Running total not running correctly when using Userelationship and having inactive relationship
Hi all, I have a problem with some of my calculations and I cannot figure out, what am I doing wrong. I want to calulate cummulative total for for values and display it in line chart: In my Table X I have all neccesarry data and I have Actual date and Forecast date. In this table I have also my calculated column which will be used for running total: measure - "unique values" (unique values = DISTINCTCOUNTNOBLANKS(findmaximumvaluesforproduct) . Since I have two different dates, I have created a calendar table, and I created relationships between my X table and calendar table. Firstly, I have created relationship Date (calendar) - actual date (x table) with active relationship And then date (calendar) - forecast dates (x table) with inactive relationship I have then used function USERELATIONSHIP to activate relationship between my forecast dates and calendar table: Forecast relationship = CALCULATE([unique values]; USERELATIONSHIP('Date'[Date]; 'x (Table)'[forecastdates])) And then I have created a running total for both forecast and actual dates: Forecast running total = CALCULATE( [forecast relationship]; FILTER( ALLSELECTED('Date'[Date]); ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC) ) ) Actual running total = CALCULATE( [unique values]; FILTER( ALLSELECTED('Date'[Date]); ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC) ) ) However, I have problem with this calculation when the forecast values end after the values for actual dates have been completed: In my example, forecast and actual values will always end up at the same value. So if the running total for actual date for one product will be 100, the forecast will be also always 100, just the dates will be different, depending how we have kept up with our schedule. So lets say actual value for product A will end on 20.10.2018 and it will display 60, that is when my line chart stops, even tough my forecast value has been only 56 on the 20.10.2018 and will be 60 on the 30.10.2018. The data are still there and when I plot only my forecast data into the plot, I can see that the graph ends at 30.10.2018, therefore the actual values should go straight for 10 days and forecast should keep increasing until 30.10.2020. However when I use this method it is ignoring those dates and the x axis stops when the actual dates (therefore the one with active relationship) reach their goal. Ignoring the dates with inactive relationship. When it is the opposite case, so when forecast values end before actual values, the forecast line goes straight until it meats it goals and theefore also meets actual values. (forecast is dashed and actual is solid). I want this view also when forecast ends after actual dates: I have tried to google this, and I have read that I should try to make both relationships inactive and therefore have two measures with USERELATIONSHIP function. So I have put both the relationships inactive and created the following calculations: actual relationship = CALCULATE([unique values]; USERELATIONSHIP('Date'[Date]; 'x(Table)'[actualdates])) Actual = CALCULATE( [actual relationship]; FILTER( ALLSELECTED('Date'[Date]); ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC) ) ) The calculation for forecasts I kept the same as is above. This change solved the problem and displayed also the forecasting values even tough they have finished after the actual values have finished, but it has created this view, where the values wont stop until the end of my calendar table: (which looks wierd if it is running straight for 10 years…) I have tried several improvement is my running total calculations, but they simply dont work… I have tried this calculation: actual1 = VAR LastDayAvailable = CALCULATE ( MAX ( 'x (Table)'[actualdates] ); ALL ( 'x(Table)') ) VAR FirstDayInSelection = MIN ( 'Date'[Date] ) VAR ShowData = (FirstDayInSelection <= LastDayAvailable) VAR Result = IF ( ShowData; CALCULATE( [actual relationship]; FILTER( ALLSELECTED('Date'[Date]); ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC) ) ) ) RETURN Result forecast1 = VAR LastDayAvailable = CALCULATE ( MAX ( 'x(Table)'[forecastdates] ); ALL ( 'x(Table)') ) VAR FirstDayInSelection = MIN ( 'Date'[Date] ) VAR ShowData = (FirstDayInSelection <= LastDayAvailable) VAR Result = IF ( ShowData; CALCULATE( [forecast relationship]; FILTER( ALLSELECTED('Date'[Date]); ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC) ) ) ) RETURN Result Also adding the condition at the end of my running total calculation Actual = CALCULATE( [actual relationship]; FILTER( ALLSELECTED('Date'[Date]); ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)&&'Date'[Date]<= MAX('Date'[Date]) ) ) However none of these have worked. Therefore, I want to ask if some of you have some idea how to solve this? I am not sure if it is not problematic, that I am using calendar table, with one (or both) inactive relationships, USERELATIONSHIP function as well as running total. I have used the quick measure from Power BI, as well as tried my own calculations but none of them displayed reult I wanted, any tip? Hope it is understandable and thank you for you help 🙂 it is much appriciated!!!! 🙂 Anastasia
