Forum Discussion
Formula not aggregated
Hello,
I have two tables in DirectQuery mode. For the “Date” column from the “SMHI” table I would like to retrieve the year. I have used Year = LEFT(SMHI[Date],4) formula. Similarly I have used this formula for month: Month = RIGHT(SMHI[Date],2). As you see below my formulas are not aggregated for the whole column as I wished.
Please can you give me some tipds?
Hello @ehsanbh ,
This conversion function is not supported for use in DirectQuery mode when used in computed columns or row-level security rules (RLS). See https://docs.microsoft.com/en-us/dax/convert-function-dax https://docs.microsoft.com/en-us/dax/convert-function-dax
You may need to convert data to the data source or switch it to import mode.
If this post helps, then consider Accepting it as the solution to help other members find it more quickly.
Best regards
Dedmon Dai
@ehsanbh ,
Try right-clicking the [Column] field in the Fields list and selecting "Do Not Summarize". Then, if this field shows "Sum of [Column]" in the Values section, right-click on it here and select "Do Not Summarize" again.
I get the following output, but I think we're out of the reach of your initial requirements now:
11 Replies
- BA_Pete
Super User
Hi Anonymous ,
If I understand correctly, you would like to show every row that features in your first table in both your Year and Month tables, even if they duplicate?
If so, then this isn't how Power BI works. It's designed to group/aggregate dimensions in this way to simplify and summarise visualised data. Even if you right-click on the Year/Month field in the Values section and select 'Show items with no data', it's still going to group them like this.
Maybe if you could explain your output requirements/use-case in a bit more detail we could advise on the best way to get to that.
Pete
Thanks, and you fully understand my intention! 🙂
My final goal is to revsie these dates (format text now and can't be directly changed) in a new column for all rows.
I have used below two formulas to reach to this goal:
- Revised Data 1 = DATE(SMHI[YEAR], SMHI[Month], 1)
- Revsied Date 2 = DATE(LEFT(SMHI[Date],4),RIGHT(SMHI[Date],2),1)
As you see in the screenshoots in both cases I couldn't reach to my goal!
I get below error in both cases:
- BA_Pete
Hi Anonymous ,
Please try the following:
Column:
..periodDate = VAR builtdate = LEFT(aTable[period], 4) & "/" & RIGHT(aTable[period], 2) &"/"& "01" RETURN CONVERT(builtdate, DATETIME)Measure:
_periodDate = VAR builtdate = LEFT(MAX(aTable[period]), 4) & "/" & RIGHT(MAX(aTable[period]), 2) &"/"& "01" RETURN CONVERT(builtdate, DATETIME)Once I select the column and the measure in the field list on the right of the screen and adjust the format in the ribbon, I get the following output:
Pete
- amitchandak
Anonymous , This seems correct. What is wrong with this data. You create three different visual table and put date , year and month and they are showing distinct values
CONVERT doesn't seem to be a function in DAX. Please can you advise if I have applied your tips correctly?
