New column witth conditions from two tables

[FredericoMM]

Hi all,

I have two tables: Calendar and CountriesHolidays:

I have to combine them to in order to create a new column in the Calendar table where I must assign a '0' when it's a Holiday in Portugal and Brazil. I've tried different solutions, but nothing seems to result. Joining tables through PowerQuery isn't possible cause I do not have access to the source files.

Any ideas? Thanks very much

[MFelix]

Hi Frederico,

Add the following colums to your data:

Table Holidays new column:

Is_country = IF('Table'[Country] in {"Portugal"; "Brazil"};1 ; 0)

Table Calendar new column:

Is Holiday = 
LOOKUPVALUE('Table'[Holiday];'Table'[Date];'calendar'[Date];'Table'[Is_country];1)

Or a value with 1 

Is Holiday = 
LOOKUPVALUE('Table'[Is_country];'Table'[Date];'calendar'[Date];'Table'[Is_country];1)

then  you can filter out on your measure the columns is holiday = 1

[FredericoMM]

Hi, @MFelix 

Thank you for your fast solution.

Unfortunately, the second part of it isn't working properly... It shows 1 and 0 randomly, apparently.

Basically I need that column 'Alocação', to show '0' instead of '8' when whether it is a holiday in PT and BR:

Thanks in advance! 

[MFelix]

Hi @FredericoMM ,

The last parameter on your formula is missing so it's not searching for the countries that are marked in the holiddays table you have:

Is Holiday = 
LOOKUPVALUE(CountriesHolidays[Is_country];CountriesHolidays[ds_holidays];'calendar'[Date].Date;1)

The formula must be:

Is Holiday = 
LOOKUPVALUE(CountriesHolidays[Is_country],CountriesHolidays[ds_holidays],'calendar'[Date].Date,CountriesHolidays[Is_country],1)

[FredericoMM]

Thanks, @MFelix 

When I paste the formula to create a new column and then I filter for '1' , it just seems to have 3 rows:

This is not correct...

[MFelix]

Hi @FredericoMM ,

Is your formula on the holidays table giving you the correct values for the dates that are in Portugal and Brazil?

The column you are creating is on the Calendar table correct? (sorry for asking)

Make a small adjustment and try this one.

Is Holiday = 
LOOKUPVALUE(CountriesHolidays[Is_country],CountriesHolidays[ds_holidays],'calendar'[Date],CountriesHolidays[Is_country],1)

If this doesn't work are you abble to share a sample file so I can check if there is something else with the structure of your data?

If the information is sensitive you can share it in private message (the files can be share by onedrive, googledrive, we transfer or similar links).

[FredericoMM]

Yes, Is_Holiday is in my Calendar table.

It stills get me only 3 row with '1':

How should I send you, @MFelix ?

[MFelix · Accepted solution]

Hi @FredericoMM ,

The issue was that in my test file I did not had repeated values in the dates for holidays now it works properly:

Use the formula below:

Is Holiday = 
IF (
    COUNTROWS (
        FILTER (
            CountriesHolidays;
            CountriesHolidays[ds_holidays] = 'Calendar'[Date]
                && CountriesHolidays[Is_country] = 1
        )
    ) >= 1;
    1;
    0
)

[MFelix]

Hi @FredericoMM ,

Can you send it trough private message on a onedrive, google drive, wetransfer or similar link, please.

[amitchandak]

@FredericoMM , pivot on country and try have holiday columns country wise and then join on date.  otherwise left join on the date table should work

https://radacad.com/pivot-and-unpivot-with-power-bi

[FredericoMM]

Hi,

I can't pivot cause I don't have access to the sources, therefore I am not able to use PowerQuery for that.

I tried to left join as you suggested, but I couldn't do it. How should I left join this case?

Thanks!