Using ALL function with relationship
Hi, I am trying to calculate how many question completed by user. For example in this month there are 10 people and 3 of the answered question-1 and 5 of the answered question-2 so question-1 Rates should be 0.3 and question-2: 0.5 My formula like that IF( HASONEVALUE(DateSlicer[period]) && SELECTVALUE(DateSlicer[period]) = "month"), CALCULATE(CALCULATE(DISTINCTCOUNT(Table[userID]),Table[Category] = "a", Table[Questions] =MAX(Table[Questions])/CALCULATE( DISTINCTCOUNT(Table[userID]), Table[Category] = "a", ALL(Table),USERELATIONSHIP(DateSlicer[DateFormats], DateTable[year])) But in this case I am using ALL functional but it smashing to USERELATIONSHIP Function. So I can not calculate right. How can do it with using this USERELATIONSHIP function?
Trouble using DAX to calculate properly between multiple active/inactive relationships
Hello, I'm trying to understand if leveraging the Power BI relationships is a good place to do this, or if it's better suited to something like Power Query, or if we just write a database query that gives the proper output. We've been trying to get away for having so many custom views in the database so I'm trying with in app relationships first. In my scenario, the data source is a relational database with several tables. We have unique IDs tied to individual users, and there is a People Table that holds all of the user information such as their Names. I have to retrieve the names for multiple fields, which means Power BI needs a mix of active and inactive relationships established. Screenshots of the relationships are at the bottom of the post. Another workaround we thought of that I don't like is having multiple/separate queries pull the ID and Name from the people table and we have an active relationship and a new query for every named individual I'm trying to surface. The main active relationship I started with was joining 'report er_log' [lead_project_manger_id] to 'report people' [id]. I'm using a simple IF statement that successfully returns the Lead Project Manager name when those two IDs match - success! Lead PM Name = IF('report er_log'[lead_project_manager_id]=RELATED('report people'[id]),RELATED('report people'[full_name])) The issue comes in for the inactive relationship between 'report activity' [record_owner_id] to 'report people' [id]. Any formula I've used that didn't throw a relationship error will ignore the inactive relationship entirely and just return the PM Name. e.g. my latest attempt was using LOOKUPVALUE. my understanding is that LOOKUPVALUE should ignore relationships but it definitely doesnt and just gives me PM names and not all record owners LOOKUPVALUE('report people'[display_name],'report people'[id],'report activity'[record_owner_id]) My first attempt was using a simple IF statement. this also just returns PM names and has issues because RELATED only likes the active relationships IF('report activity'[record_owner_id]=RELATED('report people'[id]),RELATED('report people'[full_name])) The only thing i found that might work for the USERLATIONSHIP function throws an error because in this context there's no active relationship with report people CALCULATE(RELATED('report people'[display name]),USERELATIONSHIP('report activity'[record_owner_id],'report people'[id])) Any ideas are appreciated. I'm starting to play with Power Query, and worst case I think my workaround will be fine... I just don't want to have so many distinct queries going to report people if we can help it. EDIT: I tried the workaround for funsies and it seems like the same relationship issue with the original people table is still causing heartache.
Confusion with Filter / Context
Hi I am confused with some results using an explicit filter rather than an implicit filter. I have one fact table (ASB cases) and a Date table. The two tables have an active relationship between Calendar[Date] and ASB[Reported date]. I need a measure based on a Call Due date, where the call is made on time so am using USERELATIONSHIP. I have got the measure working based on the following. Initial Response on Time = CALCULATE(COUNTROWS('ASB Case'), USERELATIONSHIP('ASB Case'[Call Due to Reporting Party], 'Calendar'[Date]), 'ASB Case'[Contacted Reporting Party On Time?] = "On-Time" ) My initial Dax which returned incorrect results was Broken Initial Response on Time = CALCULATE(COUNTROWS('ASB Case'), USERELATIONSHIP('ASB Case'[Call Due to Reporting Party], 'Calendar'[Date]), FILTER('ASB Case','ASB Case'[Contacted Reporting Party On Time?] = "On-Time")) Clearly the way the filter is being used is wrong, but I can't really see the difference. I know Filter removes existing related filters first - so if anything was to happen I would have expected to see even more cases? If anyone can help I'd be really grateful. I'd also welcome any advice on steps I could take to debug the issue. Many thanks in advanve
Dax USERELATIONSHIP not working
I put together as a simple example of an issue that I'm having with the UseRelationship dax measure not working. The problem is I have a date table where the date column is the unique key and we have a few more columns off to the side that map "Date" to its respective prior year date so that we can use the “UseRelationship” calculation to look at prior date values for a given date selected. In our dates table there are 2 additional columns that look back one year from the current date. One day called [Minus1Year] and is just subtracting one year from the date therefore there is a one to one relationship between date and minus one year. However, some dates are tagged to the same “date one year ago” value which is a separate column. For example, leap year. In the example I provide you will see that there are a few date rows that are mapped to the same “date one year ago” value and for the “minus one year” those values are unique and different. See highlighted example below. What is interesting is that if I build a dax measure that leverages the "date one year ago" relationship the relationship does not work because it is a many-Many relationship with the detail sales table, however, if there is a one to many relationship then the UseRelationship calculation works. As you can see in the example below I have a column for dates and I have a column for date one year ago which does have repeating values for some dates hence January 4th 2019 shares the same date one year ago with January 5th and January 6th of 2019. The revenue column is simply is a sum of the revenue for the given date. No issue here…. Revenue = SUM(Sales[Amount]) The "Date one year ago" is calculating the revenue amount but using the relationship between date one year ago from the dates table connecting to the transaction date column of the sales table. This calculation is not working properly and is only returning the revenue value. So you can see date one year ago revenue is equal to the revenue column. The minus one year revenue column seems to be calculating properly since that is a unique field from the dates table. How do I fix the date one year ago revenue measure so that given a specific date it uses the respective date one year ago and returns the revenue value from that time similar to how minus one revenue works today. What I would expect to see is that on the values for January 4th 5th and 6th of 2019 they share the same revenue values since the “date one year ago” is the same for all three. Why isn’t this working and how can I get this to work? Below are a few screenshots to help. Here is the model. As you can see all fields from the dates table tie to the transaction date column of the sales fact table. Here is a view of the dates table. As you can see for some of the dates they share the same “date one year ago” value. This is done by design because some dates need to share the same “date one year ago” to account for leap year or other reporting dates. Here is a quick crosstab view showing by date and their respective “date one year ago” and “minus one year” columns. The values in the “DateOneYear – Revenue” are not correct and are simply equaling the Revenue column. As an example, below you can see for January 4th 5th and 6th of 2019, the "date one year ago" was January 4th 2018….so I would expect to see for each of these three rows in the date one year ago revenue column the value of $13.2 million... unfortunately it's simply equaling the revenue. Why is this not working while the “Minus1Year – Revenue” column is working and how do I fix this? Here are the formulas for each of the 3 measures above. Hoping this is an easy answer…. DateOneYearAgo - Revenue = CALCULATE([Revenue],USERELATIONSHIP(Dates[DateOneYearAgo],Sales[TnxDate])) Minus1Year - Revenue = CALCULATE([Revenue],USERELATIONSHIP(Dates[Minus1Year],Sales[TnxDate])) Revenue = SUM(Sales[Amount]) Here are the expected results which I am not able to see... Thougths on what I am doing wrong and what is the fix for the "DateOneYearAgo - Revenue" measure?
More than just using USERELATIONSHIP(), it is complicated, need help
Hi experts, I am experiencing something more than just using simple USERELATIONSHIP(). The situation is like this: As you can see the relationship between 4 and 5 (yellow one) is inactive, but I need it to be active in order to use the yellow attribute in table 4 as a filter to apply to table 1 in my drill-through report. I've tried to change the direction from single to both, but it doesn't allow me. I've tried to use CALCULATE (EXPRESSION1, USERELATINOSHIP()), but I don't need to do any calculation in table 5, so I got stucked (BTW, it doesn't allowe me choose any attributes in table 5). I guess my ultimate goal is to make the red arrow pointed inactive relationship active. Can anyone help? Thanks.
