All Selected is not working properly, I have to include another Column
Hello, I have a fact table that has multiple colums: Item,Period_Name,Period_ID,Sales Period name is like Jan 2022 , Feb 2022 etc Period_ID is like 202201 , 202202 etc I created one slicer in the dashboard that has "Period_Name" Field and created 1 grid in the dashboard in which I want to include 4 columns: Item, Period, Sales Value, Sales value (for the all selected periods) , Sales Value (for all the periods selected and unselected) I created the measures like this Total Value Sales = CALCULATE(SUM(Fact_Sales[Value Sales])) Total Value Sales (Selected Period) = CALCULATE([Total Value Sales], ALLSELECTED(Fact_Sales[Period])) Total Value Sales (Ignore Period) = CALCULATE([Total Value Sales], ALL(Fact_Sales[Period])) But I got wrong results like the image I tried so much to solve it and solved successfully by changing in the measures Total Value Sales (Selected Period) = CALCULATE([Total Value Sales], ALLSELECTED(Fact_Sales[Period]),ALLSELECTED(Fact_Sales[PERIOD_ID])) Total Value Sales (Ignore Period) = CALCULATE([Total Value Sales], ALL(Fact_Sales[Period]),ALL(Fact_Sales[PERIOD_ID])) As you can see, numbers now are reflecting correctly... but I am unable to understand the behaviour, why it didn't work although I am not using this Period_ID column anywhere? also I wanted to create another sheet with dummy data to upload here to the community to make things easier, but surprisingly it worked successfully without adding the second condition. Does anyone have a clue? Thanks. John Elmasry
Get User selected dates in date range
I have a DAX function to retrieve teh date range that user has selected Dates = var _max = maxx(allselected('Candidates'[CreateDate]),'Candidates'[CreateDate]) var _min = minx(allselected('Candidates'[CreateDate]),'Candidates'[CreateDate]) return _min User selected date is I'd like to get 6/1/2022 and 7/31/2022 in my DAX function. However, I notice that with the above DAX I am getting 6/4/2022 as the minimum as the earliest data in my table for this range is dated as 6/4/2022. Is there a way to get 6/1/2022 and 7/31/2022 or whatever user selects in that date filter? Thanks!
include filters in allselected function
Hello, I need to apply the filter (from filter pane) to the allselected function. I am aware that using values function gives the count but I need the function to return all the rows in a table, or all the values in a column, based on the filter selection for other calculations/measures. Below is the sample dataset: YearClientProject 2020 A 1 2021 A 2 2022 A 3 2019 B 1 2018 B 2 2020 B 3 2021 B 4 is it possible to get return all the rows that are matching only the selected years in slicer (respecting the filters applied). Thanks, AnthonyJosephWeek over Week difference with slicer to change the "current date"
I am trying to calculate the 1 week change in an index level (Yield Bps) based on a selected date within a slicer. In my data, "Date" in Data table represents the current date in the slicer, calendar DatePreviousWeek in date table represents the 1 week from selected date. How can i show the change from these two fields for Yield (bps)? Below is an example of my data. The Field "Calendar DatePreviousWeek" comes from the Date table. The rest of the fields are within the data table. The data and date table share a one to one relationship with each other. Slicer = Date Field in Data Table calculation is simply (Yield where date = selected date in slicer) - (yield where date = Calendar DatePreviousWeek) Index Identifier DATE Calendar DatePreviousWeek Yield (Bps) BAMLC0A0CM 1/3/2022 0:00 12/27/2021 0:00 97 BAMLC0A0CM 1/4/2022 0:00 12/28/2021 0:00 96 BAMLC0A0CM 1/5/2022 0:00 12/29/2021 0:00 95 BAMLC0A0CM 1/6/2022 0:00 12/30/2021 0:00 95 BAMLC0A0CM 1/7/2022 0:00 12/31/2021 0:00 95 BAMLC0A0CM 1/10/2022 0:00 1/3/2022 0:00 96 BAMLC0A0CM 1/11/2022 0:00 1/4/2022 0:00 96 BAMLC0A0CM 1/12/2022 0:00 1/5/2022 0:00 96 BAMLC0A0CM 1/13/2022 0:00 1/6/2022 0:00 97 BAMLC0A0CM 1/14/2022 0:00 1/7/2022 0:00 97 BAMLC0A0CM 1/17/2022 0:00 1/10/2022 0:00 97 BAMLC0A0CM 1/18/2022 0:00 1/11/2022 0:00 98 BAMLC0A0CM 1/19/2022 0:00 1/12/2022 0:00 99 BAMLC0A0CM 1/20/2022 0:00 1/13/2022 0:00 100 BAMLC0A0CM 1/21/2022 0:00 1/14/2022 0:00 103 BAMLC0A0CM 1/24/2022 0:00 1/17/2022 0:00 105 BAMLC0A0CM 1/25/2022 0:00 1/18/2022 0:00 105 BAMLC0A0CM 1/26/2022 0:00 1/19/2022 0:00 103 BAMLC0A0CM 1/27/2022 0:00 1/20/2022 0:00 107 BAMLC0A0CM 1/28/2022 0:00 1/21/2022 0:00 109 BAMLC0A0CM 1/31/2022 0:00 1/24/2022 0:00 110 BAMLC0A0CM 2/1/2022 0:00 1/25/2022 0:00 108
DAX Measures ALL, REMOVEFILTERS
Greetings. I have a filter slicer with year, month and week number of year. From a week selection (lets say 38 which is a week in September) I need a visual to show all the applications made in that month. If I select a week in August I want all applications made in August regardless of the week. This is for September, week 38 I've used ALLSELECTED to keep the filter coming from the year and the month, but I want to remove filter from the weeknumberofyear. Yet it doesn't work. I have tried REMOVEFILTERS as well to weeknumber column but it is not working. Can you share some thoughts? Thank youGet the average of a column value based on total count of values in ID column
Hi, In a table i have column called "Object" which runs multiple times in a day and that runtime is captured in "StartTime" & "EndTime" columns, and the difference between the start and end time is captured in the "Duration" Column. Each object will be having multiple runid's which is captured in "RunID" column which is a unique value. For eg: Object AAA has two runid's called "111,222" and Object BBB has one runid called "333". Now i would like to get the average of each duration by count of that particular runid which is captured in "New Duration" column. For eg: The count of 111 runid is '10'. so for all those durations with runid 111 should be divided by the 10. The count of 222 runid is '6'. so for all those durations with runid 222 should be divided by the 6. The count of 333 runid is '8'. so for all those durations with runid 333 should be divided by the 8. Sample screenshot for above query: So since my Duration is "10" and total count of runid (111) is 10. So 10/10=1 that is my newduration.Similarly for all durations with runid's 111 should be divided bt 10. How to achieve this using DAX query?? I am connecting to this table in powerbi via SQL direct query mode. Thanks.
