Forum Discussion
Calculated Field from Tableau on Power BI
Hello guys,
Do you know how I could create this field from Tableau in Power BI using DAX?
{ FIXED [Id Estacaometeo], [Estação], (DATEPART('year', [Data])*10000 + DATEPART('month', [Data])*100 + DATEPART('day', [Data])): COUNTD(IF [Cod Mnemonico] = 'PRTH' AND (NOT ISNULL([Valor Medição])) THEN [Id Medicao] END)}
the FIXED function is sort of like a Group by, So I want to DISTINCT COUNT and group by these attributes provided at the beginning, including this crazy date calculation.
any help will be much appreciated.
4 Replies
- Greg_Deckler
Community Champion
Anonymous Sounds like you could use SUMMARIZE, SUMMARIZECOLUMNS or GROUPBY for FIXED. This part:
(DATEPART('year', [Data])*10000 + DATEPART('month', [Data])*100 + DATEPART('day', [Data]))
Should be:
YEAR('Table'[Date])*10000 + MONTH('Table'[Date])*100 + DAY('Table'[Date])
or, if in a measure you would need an aggregator so maybe something like:
VAR __Date = MAX('Table'[Date])
RETURN YEAR(__Date)*10000 + MONTH(__Date)*100 + DAY(__Date)
The IF statement would be:
IF( [Cod Mnemonico] = "PRTH" && NOT(ISBLANK([Valor Medicao])), [Id Medicao])
Not sure what COUNTD does exactly.
Probably easier if you just posted sample data and expected output.
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. 
CNENFRNLThat dumb [DATE] conversion is nothing but redundancy, #2022-5-30# never repeats itself in human history; thus no need at all to convert it to 20220530.
Add a calculated column in the first place,
CC = IF [Cod Mnemonico] = 'PRTH' AND (NOT ISBLANK([Valor Medição])) THEN [Id Medicao]then, the lod expression, from DAX perspective, returns a table, which roughtly equals to
SUMMARIZECOLUMNS( tbl[Id Estacaometeo], tbl[Estação], tbl[Data], "@CNTD", DISTINCTCOUNT(tbl[CC] )Hi CNENFRNL the results were achieved as expected, the thing is that I need to use the result of this COUNT into another calculated field, which I'm not being able to do considering we've created a new table with SUMMARIZECOLUMNS, even when I try to create a relationship between the two tables involved and use the RELATED function it doesn't recognize the relationship between them, would it be possible to achieve the same results of the SUMMARIZECOLUMNS but using a measure?
- CNENFRNL
When it comes to PBI calculation, any talk without data model is nothing but gibberish. Paste your full mockup dataset and articulate the logic. Remember to translate all headers into English or French.
